postgres의 기본 키 시퀀스가 동기화되지 않을 때 재설정하는 방법은 무엇입니까?

Programming

postgres의 기본 키 시퀀스가 동기화되지 않을 때 재설정하는 방법은 무엇입니까?

procodes 2020. 2. 14. 23:51

postgres의 기본 키 시퀀스가 동기화되지 않을 때 재설정하는 방법은 무엇입니까?

기본 키 시퀀스가 테이블 행과 동기화되지 않는 문제가 발생했습니다.

즉, 새 행을 삽입하면 직렬 데이터 형식에 암시 된 시퀀스가 이미 존재하는 숫자를 반환하기 때문에 중복 키 오류가 발생합니다.

가져 오기 / 복원이 순서를 올바르게 유지 보수하지 않아서 발생하는 것 같습니다.

-- Login to psql and run the following

-- What is the result?
SELECT MAX(id) FROM your_table;

-- Then run...
-- This should be higher than the last result.
SELECT nextval('your_table_id_seq');

-- If it's not higher... run this set the sequence last to your highest id. 
-- (wise to run a quick pg_dump first...)

BEGIN;
-- protect against concurrent inserts while you update the counter
LOCK TABLE your_table IN EXCLUSIVE MODE;
-- Update the sequence
SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);
COMMIT;

출처-Ruby 포럼

pg_get_serial_sequence시퀀스 이름에 대한 잘못된 가정을 피하기 위해 사용할 수 있습니다. 이렇게하면 시퀀스가 한 번에 재설정됩니다.

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1);

더 간결하게 :

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;

그러나 max (id)가 null이므로이 양식은 빈 테이블을 올바르게 처리 할 수 없으며 시퀀스 범위를 벗어나므로 0을 설정할 수 없습니다. 이에 대한 한 가지 해결 방법은 ALTER SEQUENCE구문 즉,

ALTER SEQUENCE table_name_id_seq RESTART WITH 1;
ALTER SEQUENCE table_name_id_seq RESTART; -- 8.4 or higher

그러나 ALTER SEQUENCE시퀀스 이름 및 재시작 값은 표현식이 될 수 없으므로 제한적으로 사용됩니다.

가장 유용한 다목적 솔루션은 setval세 번째 매개 변수로 false 를 호출 하여 "사용할 다음 값"을 지정할 수있게하는 것 같습니다.

SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

이것은 내 모든 상자를 틱합니다.

실제 시퀀스 이름을 하드 코딩하지 않습니다
빈 테이블을 올바르게 처리
기존 데이터로 테이블을 처리하고 시퀀스에 구멍을 남기지 않습니다.

마지막으로, pg_get_serial_sequence열이 시퀀스를 소유 한 경우에만 작동합니다. 증분 열이 serial유형 으로 정의 된 경우에 해당하지만 순서를 수동으로 추가 한 경우 ALTER SEQUENCE .. OWNED BY에도 수행해야합니다.

즉, serialtype이 테이블 생성에 사용 된 경우 모두 작동해야합니다.

CREATE TABLE t1 (
  id serial,
  name varchar(20)
);

SELECT pg_get_serial_sequence('t1', 'id'); -- returns 't1_id_seq'

-- reset the sequence, regardless whether table has rows or not:
SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

그러나 시퀀스가 수동으로 추가 된 경우 :

CREATE TABLE t2 (
  id integer NOT NULL,
  name varchar(20)
);

CREATE SEQUENCE t2_custom_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE t2 ALTER COLUMN id SET DEFAULT nextval('t2_custom_id_seq'::regclass);

ALTER SEQUENCE t2_custom_id_seq OWNED BY t2.id; -- required for pg_get_serial_sequence

SELECT pg_get_serial_sequence('t2', 'id'); -- returns 't2_custom_id_seq'

-- reset the sequence, regardless whether table has rows or not:
SELECT setval(pg_get_serial_sequence('t2', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

짧고 빠른 방법 :

SELECT setval('tbl_tbl_id_seq', max(tbl_id)) FROM tbl;

tbl_id시퀀스 (기본 자동 이름) 에서 그리는 serialtable 의 열입니다 .tbltbl_tbl_id_seq

연결된 시퀀스의 이름을 모르는 경우 (기본 형식 일 필요는 없음) 다음을 사용하십시오 pg_get_serial_sequence().

SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id'), max(tbl_id)) FROM tbl;

여기서는 오류가 없습니다. 문서 당 :

두 매개 변수 형식은 시퀀스의 last_value필드를 지정된 값으로 설정하고 해당 is_called필드를 true로 설정 합니다. 즉, 다음 nextval은 값을 반환하기 전에 시퀀스 를 진행시킵니다 .

대담한 강조 광산.

이 경우 테이블이 비어 있고 실제로 1부터 시작하는 경우 :

SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id')
            , COALESCE(max(tbl_id) + 1, 1)
            , false)
FROM tbl;

0시퀀스의 하한 이 기본적으로 1 (사용자 정의되지 않은 경우) 이기 때문에 2 파라미터 형식을 사용하여 시작할 수 없습니다 .

동시성

동시 쿼리 활동에 대한 방어는 없지만 위 쿼리의 테이블에 대한 쓰기는 아직 없습니다. 관련이있는 경우 테이블 을 단독 모드로 잠글 수 있습니다 . 동기화를 시도하는 동안 동시 트랜잭션이 더 많은 수를 쓰지 않도록합니다. (또한 최대 수를 망칠 수없는 무해한 쓰기를 일시적으로 차단합니다.)

그러나 기본 테이블을 잠그지 않고 미리 시퀀스 번호를 가져 왔을 수있는 클라이언트는 고려하지 않습니다 (이는 발생할 수 있음). 이를 가능하게하려면 시퀀스의 현재 값만 늘리고 절대 줄이지 마십시오. 그것은 편집증처럼 보일지 모르지만 그것은 시퀀스의 본질과 일치하고 동시성 문제를 방어합니다.

BEGIN;

LOCK TABLE tbl IN EXCLUSIVE MODE;

SELECT setval('tbl_tbl_id_seq', max(tbl_id))
FROM   tbl
HAVING max(tbl_id) > (SELECT last_value FROM tbl_tbl_id_seq);

COMMIT;

이렇게하면 테이블 또는 열 이름에 대한 가정이없는 모든 시퀀스가 공개적으로 재설정됩니다. 버전 8.4에서 테스트

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text, sequence_name text) RETURNS "pg_catalog"."void" AS 

    $body$  
      DECLARE 
      BEGIN 

      EXECUTE 'SELECT setval( ''' || sequence_name  || ''', ' || '(SELECT MAX(' || columnname || ') FROM ' || tablename || ')' || '+1)';



      END;  

    $body$  LANGUAGE 'plpgsql';


    select table_name || '_' || column_name || '_seq', reset_sequence(table_name, column_name, table_name || '_' || column_name || '_seq') from information_schema.columns where column_default like 'nextval%';

~~ALTER SEQUENCE sequence_name RESTART WITH (SELECT max (id) FROM table_name);~~ 작동하지 않습니다.

@tardate 답변에서 복사 :

SELECT setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;

postgresql에서 자동 생성 키 시퀀스 값만 변경하는 명령

ALTER SEQUENCE "your_sequence_name" RESTART WITH 0;

0 대신 시퀀스를 다시 시작하려는 숫자를 입력 할 수 있습니다.

기본 시퀀스 이름은 "TableName_FieldName_seq"입니다. 예를 들어, 테이블 이름이 "MyTable"있고 필드 이름이 "MyID"인 경우 시퀀스 이름은입니다 "MyTable_MyID_seq".

이것은 @murugesanponappan의 답변과 동일하지만 솔루션에 구문 오류가 있습니다. 명령 (select max()...)에서 하위 쿼리 를 사용할 수 없습니다 alter. 따라서 고정 숫자 값을 사용하거나 하위 쿼리 대신 변수를 사용해야합니다.

각 테이블의 기본 키가 "id"인 것을 제외하고 이름에 대한 가정없이 모든 시퀀스를 재설정하십시오.

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text)
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
    EXECUTE 'SELECT setval( pg_get_serial_sequence(''' || tablename || ''', ''' || columnname || '''),
    (SELECT COALESCE(MAX(id)+1,1) FROM ' || tablename || '), false)';
END;
$body$  LANGUAGE 'plpgsql';

select table_name || '_' || column_name || '_seq', reset_sequence(table_name, column_name) from information_schema.columns where column_default like 'nextval%';

이러한 함수는 시퀀스 이름, 열 이름, 테이블 이름 또는 스키마 이름에 공백, 문장 부호 등과 같은 재미있는 문자가있을 때 위험에 처하게됩니다. 나는 이것을 썼다 :

CREATE OR REPLACE FUNCTION sequence_max_value(oid) RETURNS bigint
VOLATILE STRICT LANGUAGE plpgsql AS  $$
DECLARE
 tabrelid oid;
 colname name;
 r record;
 newmax bigint;
BEGIN
 FOR tabrelid, colname IN SELECT attrelid, attname
               FROM pg_attribute
              WHERE (attrelid, attnum) IN (
                      SELECT adrelid::regclass,adnum
                        FROM pg_attrdef
                       WHERE oid IN (SELECT objid
                                       FROM pg_depend
                                      WHERE refobjid = $1
                                            AND classid = 'pg_attrdef'::regclass
                                    )
          ) LOOP
      FOR r IN EXECUTE 'SELECT max(' || quote_ident(colname) || ') FROM ' || tabrelid::regclass LOOP
          IF newmax IS NULL OR r.max > newmax THEN
              newmax := r.max;
          END IF;
      END LOOP;
  END LOOP;
  RETURN newmax;
END; $$ ;

OID를 전달하여 단일 시퀀스를 호출 할 수 있으며 시퀀스가 기본값 인 테이블에서 사용되는 가장 높은 숫자를 반환합니다. 또는 다음과 같은 쿼리로 실행하여 데이터베이스의 모든 시퀀스를 재설정 할 수 있습니다.

 select relname, setval(oid, sequence_max_value(oid))
   from pg_class
  where relkind = 'S';

다른 품질을 사용하면 특정 스키마의 시퀀스 만 재설정 할 수 있습니다. 예를 들어 "public"스키마에서 시퀀스를 조정하려는 경우 :

select relname, setval(pg_class.oid, sequence_max_value(pg_class.oid))
  from pg_class, pg_namespace
 where pg_class.relnamespace = pg_namespace.oid and
       nspname = 'public' and
       relkind = 'S';

setval ()의 작동 방식으로 인해 결과에 1을 추가 할 필요가 없습니다.

마지막으로, 일부 데이터베이스에는 시스템 카탈로그가 데이터베이스에 대한 전체 정보를 갖지 못하게하는 방식으로 시퀀스에 연결되는 기본값이있는 것 같습니다. 이것은 psql의 \ d에서 이와 같은 것을 볼 때 발생합니다.

alvherre=# \d baz
                     Tabla «public.baz»
 Columna |  Tipo   |                 Modificadores                  
---------+---------+------------------------------------------------
 a       | integer | default nextval(('foo_a_seq'::text)::regclass)

해당 기본 절의 nextval () 호출에는 :: regclass 캐스트 외에 :: text 캐스트가 있습니다. 내가 생각하는 데이터베이스가 이전의 PostgreSQL 버전에서 pg_dump'ed되기에이 때문이다. 일어날 함수는 위의 sequence_max_value () 함수가 그러한 테이블을 무시한다는 것입니다. 문제를 해결하기 위해 캐스트없이 직접 시퀀스를 참조하도록 DEFAULT 절을 재정의 할 수 있습니다.

alvherre=# alter table baz alter a set default nextval('foo_a_seq');
ALTER TABLE

그런 다음 psql이 올바르게 표시합니다.

alvherre=# \d baz
                     Tabla «public.baz»
 Columna |  Tipo   |             Modificadores              
---------+---------+----------------------------------------
 a       | integer | default nextval('foo_a_seq'::regclass)

이를 수정하자마자 함수는이 테이블과 동일한 시퀀스를 사용하는 다른 모든 테이블에 대해 올바르게 작동합니다.

공개에서 모든 시퀀스 재설정

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text) RETURNS "pg_catalog"."void" AS 
$body$  
  DECLARE 
  BEGIN 
  EXECUTE 'SELECT setval( ''' 
  || tablename  
  || '_id_seq'', ' 
  || '(SELECT id + 1 FROM "' 
  || tablename  
  || '" ORDER BY id DESC LIMIT 1), false)';  
  END;  
$body$  LANGUAGE 'plpgsql';

select sequence_name, reset_sequence(split_part(sequence_name, '_id_seq',1)) from information_schema.sequences
        where sequence_schema='public';

내 버전은 첫 번째 버전을 사용하고 일부 오류 검사는 ...

BEGIN;
CREATE OR REPLACE FUNCTION reset_sequence(_table_schema text, _tablename text, _columnname text, _sequence_name text)
RETURNS pg_catalog.void AS
$BODY$
DECLARE
BEGIN
 PERFORM 1
 FROM information_schema.sequences
 WHERE
  sequence_schema = _table_schema AND
  sequence_name = _sequence_name;
 IF FOUND THEN
  EXECUTE 'SELECT setval( ''' || _table_schema || '.' || _sequence_name  || ''', ' || '(SELECT MAX(' || _columnname || ') FROM ' || _table_schema || '.' || _tablename || ')' || '+1)';
 ELSE
  RAISE WARNING 'SEQUENCE NOT UPDATED ON %.%', _tablename, _columnname;
 END IF;
END; 
$BODY$
 LANGUAGE 'plpgsql';

SELECT reset_sequence(table_schema, table_name, column_name, table_name || '_' || column_name || '_seq')
FROM information_schema.columns
WHERE column_default LIKE 'nextval%';

DROP FUNCTION reset_sequence(_table_schema text, _tablename text, _columnname text, _sequence_name text) ;
COMMIT;

여기에 실제로 하드 코어 답변이 있습니다. 여기에서 많은 답변이 버전 9.3에서 작동하지 않기 때문에 이것이 요청되었을 때 실제로는 나빴다 고 가정합니다. 버전 8.0 이후 의 문서 는이 질문에 대한 답변을 제공합니다.

SELECT setval('serial', max(id)) FROM distributors;

또한 대소 문자를 구분하는 시퀀스 이름을 관리해야하는 경우 다음과 같이하십시오.

SELECT setval('"Serial"', max(id)) FROM distributors;

postgres wiki 에서이 솔루션을 찾을 것을 제안합니다. 테이블의 모든 시퀀스를 업데이트합니다.

SELECT 'SELECT SETVAL(' ||
       quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
       quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
    AND S.oid = D.objid
    AND D.refobjid = T.oid
    AND D.refobjid = C.attrelid
    AND D.refobjsubid = C.attnum
    AND T.relname = PGT.tablename
ORDER BY S.relname;

사용 방법 (postgres wiki에서) :

이것을 파일에 저장하십시오 (예 : 'reset.sql')
파일을 실행하고 일반적인 헤더를 포함하지 않는 방식으로 출력을 저장 한 다음 해당 출력을 실행하십시오. 예:

예:

psql -Atq -f reset.sql -o temp
psql -f temp
rm temp

(또한 시퀀스 소유권에 대한 수정 포함) 원본 기사 여기

또 다른 plpgsql-다음 경우에만 재설정 max(att) > then lastval

do --check seq not in sync
$$
declare
 _r record;
 _i bigint;
 _m bigint;
begin
  for _r in (
    SELECT relname,nspname,d.refobjid::regclass, a.attname, refobjid
    FROM   pg_depend    d
    JOIN   pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
    JOIN pg_class r on r.oid = objid
    JOIN pg_namespace n on n.oid = relnamespace
    WHERE  d.refobjsubid > 0 and  relkind = 'S'
   ) loop
    execute format('select last_value from %I.%I',_r.nspname,_r.relname) into _i;
    execute format('select max(%I) from %s',_r.attname,_r.refobjid) into _m;
    if coalesce(_m,0) > _i then
      raise info '%',concat('changed: ',_r.nspname,'.',_r.relname,' from:',_i,' to:',_m);
      execute format('alter sequence %I.%I restart with %s',_r.nspname,_r.relname,_m+1);
    end if;
  end loop;

end;
$$
;

또한 줄에 주석을 달면 --execute format('alter sequence실제로 값을 재설정하지 않고 목록을 제공합니다.

이 문제는 엔티티 프레임 워크를 사용하여 데이터베이스를 생성 한 다음 초기 데이터로 데이터베이스를 시드 할 때 발생하므로 시퀀스가 일치하지 않습니다.

데이터베이스 시드 후 실행할 스크립트를 작성하여 해결했습니다.

DO
$do$
DECLARE tablename text;
BEGIN
    -- change the where statments to include or exclude whatever tables you need
    FOR tablename IN SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' AND table_name != '__EFMigrationsHistory'
        LOOP
            EXECUTE format('SELECT setval(pg_get_serial_sequence(''"%s"'', ''Id''), (SELECT MAX("Id") + 1 from "%s"))', tablename, tablename);
    END LOOP;
END
$do$

함께 모아서

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text) 
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
  EXECUTE 'SELECT setval( pg_get_serial_sequence(''' || tablename || ''', ''id''),
  (SELECT COALESCE(MAX(id)+1,1) FROM ' || tablename || '), false)';
END;
$body$  LANGUAGE 'plpgsql';

id'주어진 테이블 의 순서를 수정 합니다 (예를 들어 django와 함께 일반적으로 필요합니다).

아직 코드를 시도하지 않았기 전에 : 다음에는 Klaus 및 내 버전의 약간의 조정만으로 내 PC에서 작동하는 Klaus 및 user457226 솔루션 모두에 대한 SQL 코드 버전을 게시합니다 [Postgres 8.3]. 사용자를 위해

클라우스 솔루션 :

drop function IF EXISTS rebuilt_sequences() RESTRICT;
CREATE OR REPLACE FUNCTION  rebuilt_sequences() RETURNS integer as
$body$
  DECLARE sequencedefs RECORD; c integer ;
  BEGIN
    FOR sequencedefs IN Select
      constraint_column_usage.table_name as tablename,
      constraint_column_usage.table_name as tablename, 
      constraint_column_usage.column_name as columnname,
      replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','') as sequencename
      from information_schema.constraint_column_usage, information_schema.columns
      where constraint_column_usage.table_schema ='public' AND 
      columns.table_schema = 'public' AND columns.table_name=constraint_column_usage.table_name
      AND constraint_column_usage.column_name = columns.column_name
      AND columns.column_default is not null
   LOOP    
      EXECUTE 'select max('||sequencedefs.columnname||') from ' || sequencedefs.tablename INTO c;
      IF c is null THEN c = 0; END IF;
      IF c is not null THEN c = c+ 1; END IF;
      EXECUTE 'alter sequence ' || sequencedefs.sequencename ||' restart  with ' || c;
   END LOOP;

   RETURN 1; END;
$body$ LANGUAGE plpgsql;

select rebuilt_sequences();

user457226 솔루션 :

--drop function IF EXISTS reset_sequence (text,text) RESTRICT;
CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text,columnname text) RETURNS bigint --"pg_catalog"."void"
AS
$body$
  DECLARE seqname character varying;
          c integer;
  BEGIN
    select tablename || '_' || columnname || '_seq' into seqname;
    EXECUTE 'SELECT max("' || columnname || '") FROM "' || tablename || '"' into c;
    if c is null then c = 0; end if;
    c = c+1; --because of substitution of setval with "alter sequence"
    --EXECUTE 'SELECT setval( "' || seqname || '", ' || cast(c as character varying) || ', false)'; DOES NOT WORK!!!
    EXECUTE 'alter sequence ' || seqname ||' restart with ' || cast(c as character varying);
    RETURN nextval(seqname)-1;
  END;
$body$ LANGUAGE 'plpgsql';

select sequence_name, PG_CLASS.relname, PG_ATTRIBUTE.attname,
       reset_sequence(PG_CLASS.relname,PG_ATTRIBUTE.attname)
from PG_CLASS
join PG_ATTRIBUTE on PG_ATTRIBUTE.attrelid = PG_CLASS.oid
join information_schema.sequences
     on information_schema.sequences.sequence_name = PG_CLASS.relname || '_' || PG_ATTRIBUTE.attname || '_seq'
where sequence_schema='public';

공개 스키마 함수에서 모든 시퀀스를 다시 확인하십시오.

CREATE OR REPLACE FUNCTION public.recheck_sequence (
)
RETURNS void AS
$body$
DECLARE
  _table_name VARCHAR;
  _column_name VARCHAR;  
  _sequence_name VARCHAR;
BEGIN
  FOR _table_name IN SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public' LOOP
    FOR _column_name IN SELECT column_name FROM information_schema.columns WHERE table_name = _table_name LOOP
        SELECT pg_get_serial_sequence(_table_name, _column_name) INTO _sequence_name;
        IF _sequence_name IS NOT NULL THEN 
            EXECUTE 'SELECT setval('''||_sequence_name||''', COALESCE((SELECT MAX('||quote_ident(_column_name)||')+1 FROM '||quote_ident(_table_name)||'), 1), FALSE);';
        END IF;
    END LOOP;   
  END LOOP;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

모든 시퀀스를 1로 다시 시작하려면 다음을 사용하십시오.

-- Create Function
CREATE OR REPLACE FUNCTION "sy_restart_seq_to_1" (
    relname TEXT
)
RETURNS "pg_catalog"."void" AS
$BODY$

DECLARE

BEGIN
    EXECUTE 'ALTER SEQUENCE '||relname||' RESTART WITH 1;';
END;
$BODY$

LANGUAGE 'plpgsql';

-- Use Function
SELECT 
    relname
    ,sy_restart_seq_to_1(relname)
FROM pg_class
WHERE relkind = 'S';

초기화를 위해 사용자 지정 SQL 데이터를로드 할 때이 오류가 표시되는 경우이를 방지하는 다른 방법은 다음과 같습니다.

글을 쓰는 대신 :

INSERT INTO book (id, name, price) VALUES (1 , 'Alchemist' , 10),

id초기 데이터에서 (기본 키) 제거

INSERT INTO book (name, price) VALUES ('Alchemist' , 10),

이것은 Postgres 시퀀스를 동기화 상태로 유지합니다!

이 답변은 mauro의 사본입니다.

drop function IF EXISTS rebuilt_sequences() RESTRICT;
CREATE OR REPLACE FUNCTION  rebuilt_sequences() RETURNS integer as
$body$
  DECLARE sequencedefs RECORD; c integer ;
  BEGIN
    FOR sequencedefs IN Select
      DISTINCT(constraint_column_usage.table_name) as tablename,
      constraint_column_usage.column_name as columnname,
      replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','') as sequencename
      from information_schema.constraint_column_usage, information_schema.columns
      where constraint_column_usage.table_schema ='public' AND 
      columns.table_schema = 'public' AND columns.table_name=constraint_column_usage.table_name
      AND constraint_column_usage.column_name = columns.column_name
      AND columns.column_default is not null 
      ORDER BY sequencename
   LOOP    
      EXECUTE 'select max('||sequencedefs.columnname||') from ' || sequencedefs.tablename INTO c;
      IF c is null THEN c = 0; END IF;
      IF c is not null THEN c = c+ 1; END IF;
      EXECUTE 'alter sequence ' || sequencedefs.sequencename ||' minvalue '||c ||' start ' || c ||' restart  with ' || c;
   END LOOP;

   RETURN 1; END;
$body$ LANGUAGE plpgsql;

select rebuilt_sequences();

나는 혼합 케이스 테이블과 열을 사용하여 데이터베이스 작업을 위해 djsnowsill의 답변을 얻으려고 한 시간을 보냈고 Manuel Darveau의 의견 덕분에 마침내 솔루션을 우연히 발견했지만 모든 사람이 조금 더 명확하게 만들 수 있다고 생각했습니다.

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text)
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
EXECUTE format('SELECT setval(pg_get_serial_sequence(''%1$I'', %2$L),
        (SELECT COALESCE(MAX(%2$I)+1,1) FROM %1$I), false)',tablename,columnname);
END;
$body$  LANGUAGE 'plpgsql';

SELECT format('%s_%s_seq',table_name,column_name), reset_sequence(table_name,column_name) 
FROM information_schema.columns WHERE column_default like 'nextval%';

이것은 다음과 같은 이점이 있습니다.

ID 열이 특정 방식으로 철자를 가정하지 않습니다.
모든 테이블에 시퀀스가 있다고 가정하지는 않습니다.
대소 문자 혼합 테이블 / 열 이름 작업.
보다 간결한 형식을 사용합니다.

설명하기 위해 문제는 pg_get_serial_sequence문자열이 참조하는 것을 해결 하는 데 필요 하다는 것입니다.

"TableName" --it thinks it's a table or column
'TableName' --it thinks it's a string, but makes it lower case
'"TableName"' --it works!

이것은 ''%1$I''형식 문자열을 사용하여 달성되며 ''아포스트로피 1$는 첫 번째 인수를 I의미하며 따옴표를 의미합니다.

Klaus 답변은 가장 유용하고 약간의 실수로 실행됩니다. select 문에 DISTINCT를 추가해야합니다.

그러나 두 개의 다른 테이블에 대해 table + column 이름이 동일하지 않다고 확신하는 경우 다음을 사용할 수도 있습니다.

select sequence_name, --PG_CLASS.relname, PG_ATTRIBUTE.attname
       reset_sequence(split_part(sequence_name, '_id_seq',1))
from PG_CLASS
join PG_ATTRIBUTE on PG_ATTRIBUTE.attrelid = PG_CLASS.oid
join information_schema.sequences
     on information_schema.sequences.sequence_name = PG_CLASS.relname || '_' || PG_ATTRIBUTE.attname
where sequence_schema='public';

관심있는 열 이름이 'ID'가 아닌 경우 user457226 솔루션의 확장입니다.

추악한 해킹으로 쉘 마법을 사용하여 해결하십시오. 훌륭한 솔루션은 아니지만 비슷한 문제가있는 다른 사람들에게 영감을 줄 수 있습니다. :)

pg_dump -s <DATABASE> | grep 'CREATE TABLE' | awk '{print "SELECT setval(#" $3 "_id_seq#, (SELECT MAX(id) FROM " $3 "));"}' | sed "s/#/'/g" | psql <DATABASE> -f -

select 'SELECT SETVAL(' || seq [ 1] || ', COALESCE(MAX('||column_name||')+1, 1) ) FROM '||table_name||';'
from (
       SELECT table_name, column_name, column_default, regexp_match(column_default, '''.*''') as seq
       from information_schema.columns
       where column_default ilike 'nextval%'
     ) as sequense_query

SELECT setval... JDBC를 bork로 만들므로 Java 호환 방법이 있습니다.

-- work around JDBC 'A result was returned when none was expected.'
-- fix broken nextval due to poorly written 20140320100000_CreateAdminUserRoleTables.sql
DO 'BEGIN PERFORM setval(pg_get_serial_sequence(''admin_user_role_groups'', ''id''), 1 + COALESCE(MAX(id), 0), FALSE) FROM admin_user_role_groups; END;';

ID로 사용되는 스키마의 모든 시퀀스를 업데이트하는 방법 :

DO $$ DECLARE
  r RECORD;
BEGIN
FOR r IN (SELECT tablename, pg_get_serial_sequence(tablename, 'id') as sequencename
          FROM pg_catalog.pg_tables
          WHERE schemaname='YOUR_SCHEMA'
          AND tablename IN (SELECT table_name 
                            FROM information_schema.columns 
                            WHERE table_name=tablename and column_name='id')
          order by tablename)
LOOP
EXECUTE
        'SELECT setval(''' || r.sequencename || ''', COALESCE(MAX(id), 1), MAX(id) IS NOT null)
         FROM ' || r.tablename || ';';
END LOOP;
END $$;

아래 명령을 실행하십시오.

SELECT setval('my_table_seq', (SELECT max(id) FROM my_table));

재 색인을 시도하십시오 .

업데이트 : 의견에서 지적했듯이 원래 질문에 대한 답변이었습니다.

참고 URL : https://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync

'Programming' 카테고리의 다른 글

jQuery를 사용하여 입력이 비어 있는지 확인하십시오. (0)	2020.02.14
Subversion에서 이전 버전의 코드로 돌아가려면 어떻게합니까? (0)	2020.02.14
잡히지 않는 오류 : 변하지 않는 위반 : 요소 유형이 잘못되었습니다 : 문자열 (내장 구성 요소의 경우) 또는 클래스 / 함수가 필요하지만 다음을 얻습니다. (0)	2020.02.14
Selenium WebDriver로 스크린 샷을 찍는 방법 (0)	2020.02.14
각 해시 맵에 대한 방법? (0)	2020.02.14

현재글postgres의 기본 키 시퀀스가 동기화되지 않을 때 재설정하는 방법은 무엇입니까?

procodes

postgres의 기본 키 시퀀스가 동기화되지 않을 때 재설정하는 방법은 무엇입니까?

postgres의 기본 키 시퀀스가 동기화되지 않을 때 재설정하는 방법은 무엇입니까?

동시성

'Programming' 카테고리의 다른 글

'Programming'의 다른글

티스토리툴바

postgres의 기본 키 시퀀스가 ​​동기화되지 않을 때 재설정하는 방법은 무엇입니까?

postgres의 기본 키 시퀀스가 ​​동기화되지 않을 때 재설정하는 방법은 무엇입니까?

동시성

'Programming' 카테고리의 다른 글

'Programming'의 다른글

관련글

티스토리툴바

postgres의 기본 키 시퀀스가 동기화되지 않을 때 재설정하는 방법은 무엇입니까?

postgres의 기본 키 시퀀스가 동기화되지 않을 때 재설정하는 방법은 무엇입니까?