Postgres의 기본 키 시퀀스가 동기화되지 않을 때 어떻게 재설정합니까?
프라이머리 키시퀀스가 테이블 행과 동기화되지 않는 문제가 발생하였습니다.
즉, 새 행을 삽입하면 시리얼 데이터 유형에 포함된 시퀀스가 이미 존재하는 번호를 반환하기 때문에 중복 키 오류가 발생합니다.
Import/Restore가 시퀀스를 제대로 유지하지 못한 것이 원인인 것 같습니다.
-- 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;
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)가 늘이기 때문에 빈 테이블을 올바르게 처리할 수 없습니다.또한 시퀀스 범위를 벗어나기 때문에 setval 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세매개 변수로 할 수 있습니다.
SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;
모든 상자에 체크 표시를 합니다.
- 실제 시퀀스 이름의 하드캐스팅을 회피합니다.
- 빈 테이블을 올바르게 처리하다
- 기존 데이터가 있는 테이블을 처리하여 시퀀스에 구멍을 남기지 않습니다.
주의해 .pg_get_serial_sequence는 시퀀스가 컬럼에 의해 소유되는 경우에만 작동합니다. 컬럼을 '보다 크다'로했을 때 됩니다.serial단, 으로 추가된 에는 type, ", "type"을 확인해야 .ALTER SEQUENCE .. OWNED BY수행도 됩니다.
즉의 경우,serial 작성에합니다.type 이 、 이 、 type 、 type type type type type 。모두 동작합니다.
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가 되다serial ★★★★★★★★★★★★★★★★★」IDENTITY의 tbl로부터 「」를 끌어냅니다tbl_tbl_id_seq(일부러)
첨부된 시퀀스의 이름을 모르는 경우(기본 형식일 필요는 없음)를 사용합니다.IDENTITY : 한한) :
SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id'), max(tbl_id)) FROM tbl;
여기에는 1개씩의 오류는 없습니다.매뉴얼:
의 2-모수 형식을 합니다.
last_value[ ] 하고 [ ]의 값을 설정합니다.is_called필드를 true로 설정합니다.즉, 값을 반환하기 전에 다음 값이 시퀀스를 진행합니다.
과감하게 강조해 주세요.
테이블이 비어 있을 경우, 이 경우 실제로 1부터 시작합니다.
SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id')
, COALESCE(max(tbl_id) + 1, 1)
, false)
FROM tbl;
하고 2-어울리지 할 수는 없어요.0시퀀스 하한이 기본적으로 1이기 때문입니다(사용자 정의되지 않은 경우).
동시 쓰기 부하 시 안전
동시 시퀀스 액티비티 또는 쓰기를 방지하기 위해 테이블을 잠급니다.SHARE모드. 동시 트랜잭션이 더 큰 숫자(또는 모든 것)를 쓰는 것을 방지합니다.
메인 테이블에 잠금이 없는 상태로 시퀀스 번호를 미리 취득했을 가능성이 있는 클라이언트(특정 설정에서는 발생할 수 있음)도 고려하려면 시퀀스의 현재 값만 늘리고 줄이지 마십시오.편집증적으로 보일 수도 있지만, 그것은 시퀀스의 본질과 일치하며 동시성 문제에 대한 방어입니다.
BEGIN;
LOCK TABLE tbl IN SHARE MODE;
SELECT setval('tbl_tbl_id_seq', max(tbl_id))
FROM tbl
HAVING max(tbl_id) > (SELECT last_value FROM tbl_tbl_id_seq); -- prevent lower number
COMMIT;
SHARE모드는 목적에 따라 충분히 강력합니다.매뉴얼:
이 모드는 동시 데이터 변경으로부터 테이블을 보호합니다.
ROW EXCLUSIVE
「」
UPDATE,DELETE, , , , 입니다.INSERT대상 테이블에서 이 잠금 모드를 획득합니다.
이렇게 하면 테이블 또는 열 이름에 대한 가정을 하지 않고 공개에서 모든 시퀀스가 리셋됩니다.버전 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_name RESTART WITH(테이블_name에서 최대(id) 선택);
효과가 없다.
@tardate 응답에서 복사:
SELECT setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;
다음 예제에서는 테이블 이름이 다음과 같습니다.users스키마명은public(기본 스키마), 필요에 따라 교체합니다.
.max id:
SELECT MAX(id) FROM public.users;
. 2. 을 합니다.next value:
SELECT nextval('public."users_id_seq"');
3. 그 3. 3 3next value max id , 리셋:
SELECT setval('public."users_id_seq"',
(SELECT MAX(id) FROM public.users)
);
주의:
nextval()는 현재 .이때 현재 값은 반환됩니다.currval()여기서 설명하는 바와 같이 현재 값을 반환합니다.
이 명령어는 postgresql에서 자동 생성된 키 시퀀스 값을 변경하는 경우에만 사용합니다.
ALTER SEQUENCE "your_sequence_name" RESTART WITH 0;
0 대신에, 시퀀스를 재기동하는 임의의 번호를 입력할 수 있습니다.
은 " " " 입니다."TableName_FieldName_seq"이 「 」인 「 」입니다."MyTable"은 「」입니다."MyID"시퀀스명은 입니다.
@murganponapan은 서브쿼리를 사용할 수 없습니다.(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%';
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
원본 문서(시퀀스 소유권에 대한 수정도 포함)는 여기에 있습니다.
시퀀스 이름, 열 이름, 테이블 이름 또는 스키마 이름에 공백, 구두점 등과 같은 재미있는 문자가 있으면 이러한 함수에 위험이 따릅니다.저는 이렇게 썼습니다.
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를 전달함으로써 단일 시퀀스에 대해 호출할 수 있습니다.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)
이 default 구의 nextval() 콜에는 ::regclass 캐스트 외에 :: 텍스트캐스트가 있습니다.이것은 오래된 Postgre에서 pg_dump' 데이터베이스가 다운로드되었기 때문이라고 생각합니다.SQL 버전위의 함수 시퀀스_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)
이 문제를 수정하면 이 테이블뿐만 아니라 동일한 시퀀스를 사용할 수 있는 다른 모든 테이블에서도 이 함수가 올바르게 작동합니다.
- plpgsql - plpgsql - plpgsql - plpgsql - plpgsql - plpgsql - 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는, 실제로 하는 가 표시됩니다.
공개에서 모든 시퀀스 재설정
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';
이 문제는 엔티티 프레임워크를 사용하여 데이터베이스를 작성한 후 초기 데이터로 데이터베이스를 시드할 때 발생합니다.이 때문에 시퀀스가 일치하지 않습니다.
데이터베이스 시드 후 실행할 스크립트를 작성하여 해결했습니다.
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$
여기에서는 몇 가지 확실한 답변이 있습니다.이 답변이 있었을 때는 매우 좋지 않았던 것 같습니다.이 답변의 대부분은 버전 9.3에서는 동작하지 않기 때문입니다.버전 8.0 이후의 메뉴얼에서는, 다음의 질문에 대한 회답이 기재되어 있습니다.
SELECT setval('serial', max(id)) FROM distributors;
또한 대소문자를 구분하는 시퀀스 이름을 처리해야 하는 경우 다음과 같이 하십시오.
SELECT setval('"Serial"', max(id)) FROM distributors;
내 버전은 첫 번째 버전을 사용하지만 오류 확인 기능이 있습니다.
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;
모든 것을 종합하면
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와 함께 필요).
공개 스키마 함수의 모든 시퀀스를 다시 확인합니다.
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;
아직 코드를 시도하지 않았습니다.아래에 제 PC [Postgres 8.3]에서 동작하는 Klaus와 user457226 솔루션용 SQL 코드 버전을 올립니다.Klaus 1과 user 457226 버전에는 약간의 조정을 가하고 있습니다.
Klaus 솔루션:
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';
이 답변은 마우로가 베낀 것입니다.
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();
초기화를 위해 커스텀 SQL 데이터를 로드할 때 이 오류가 표시되는 경우 이를 방지하는 또 다른 방법은 다음과 같습니다.
쓰는 대신:
INSERT INTO book (id, name, price) VALUES (1 , 'Alchemist' , 10),
를제 remove remove remove를 remove remove 를 remove 。id 키)첫로부터의 ('프라이머리 키')
INSERT INTO book (name, price) VALUES ('Alchemist' , 10),
그러면 Postgres 시퀀스가 동기화됩니다!
모든 시퀀스를 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';
그래서 저는 이 분야에 충분한 의견이나 재창조된 바퀴가 없다는 것을 알 수 있었습니다. 그래서 저는 모든 것에 향신료를 넣기로 결심했습니다.
다음 절차는 다음과 같습니다.
- 는 테이블과 관련된 시퀀스에 초점을 맞추고 있습니다(영향만).
- 는 시리얼 및 GENTERED AS ID 열 모두에 대해 동작합니다.
- good_column_names 및 "BAD_column_123" 이름에 대해 작동합니다.
- 테이블이 비어 있는 경우 각 시퀀스의 정의된 시작 값을 자동으로 할당합니다.
- 특정 시퀀스만 영향을 받을 수 있습니다(schema.table.column 표기).
- 프리뷰 모드가 있다
CREATE OR REPLACE PROCEDURE pg_reset_all_table_sequences(
IN commit_mode BOOLEAN DEFAULT FALSE
, IN mask_in TEXT DEFAULT NULL
) AS
$$
DECLARE
sql_reset TEXT;
each_sec RECORD;
new_val TEXT;
BEGIN
sql_reset :=
$sql$
SELECT setval(pg_get_serial_sequence('%1$s.%2$s', '%3$s'), coalesce(max("%3$s"), %4$s), false) FROM %1$s.%2$s;
$sql$
;
FOR each_sec IN (
SELECT
quote_ident(table_schema) as table_schema
, quote_ident(table_name) as table_name
, column_name
, coalesce(identity_start::INT, seqstart) as min_val
FROM information_schema.columns
JOIN pg_sequence ON seqrelid = pg_get_serial_sequence(quote_ident(table_schema)||'.'||quote_ident(table_name) , column_name)::regclass
WHERE
(is_identity::boolean OR column_default LIKE 'nextval%') -- catches both SERIAL and IDENTITY sequences
-- mask on column address (schema.table.column) if supplied
AND coalesce( table_schema||'.'||table_name||'.'||column_name = mask_in, TRUE )
)
LOOP
IF commit_mode THEN
EXECUTE format(sql_reset, each_sec.table_schema, each_sec.table_name, each_sec.column_name, each_sec.min_val) INTO new_val;
RAISE INFO 'Resetting sequence for: %.% (%) to %'
, each_sec.table_schema
, each_sec.table_name
, each_sec.column_name
, new_val
;
ELSE
RAISE INFO 'Sequence found for resetting: %.% (%)'
, each_sec.table_schema
, each_sec.table_name
, each_sec.column_name
;
END IF
;
END LOOP;
END
$$
LANGUAGE plpgsql
;
미리보기:
call pg_reset_all_table_sequences();
커밋:
call pg_reset_all_table_sequences(true);
대상 테이블만 지정하려면:
call pg_reset_all_table_sequences('schema.table.column');
Klaus의 답변은 가장 유용하며, 약간의 실수를 제외합니다.선택문에 DISTINT을 추가해야 합니다.
단, 2개의 다른 테이블에서 테이블+컬럼 이름을 사용할 수 없는 경우에는 다음과 같이 사용할 수도 있습니다.
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'가 아닌 경우에 대한 사용자 457226 솔루션의 확장입니다.
나는 djsnowsill의 답변을 Mixed Case 표와 컬럼을 사용하여 데이터베이스로 작업하기 위해 1시간을 소비했고, 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$arg, 첫 번째 arg를 의미합니다.I로 둘러싸인
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('my_table_seq', (SELECT max(id) FROM my_table));
셸 마법을 사용하여 수정하는 추악한 해킹은 좋은 해결책은 아니지만 다른 사람에게 유사한 문제를 일으킬 수 있습니다.
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 -
스키마에서 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 $$;
재인덱스를 시도합니다.
업데이트: 댓글에서 지적된 바와 같이, 이는 원래 질문에 대한 답변이었습니다.
여기 좋은 답변들이 많이 있어요.Django 데이터베이스를 새로고침한 후에도 같은 필요성이 있었습니다.
하지만 난 필요했어:
- 올인원 기능
- 한 번에 하나 이상의 스키마를 수정할 수 있습니다.
- 한 번에 모든 테이블 또는 한 테이블만 수정할 수 있습니다.
- 또한 무엇이 변했는지 또는 변하지 않았는지 정확하게 알 수 있는 좋은 방법을 원했습니다.
이것은 원래 요청의 필요성과 매우 유사해 보입니다.
Baldiry 마우로
drop function IF EXISTS reset_sequences(text[], text) RESTRICT;
CREATE OR REPLACE FUNCTION reset_sequences(
in_schema_name_list text[] = '{"django", "dbaas", "metrics", "monitor", "runner", "db_counts"}',
in_table_name text = '%') RETURNS text[] as
$body$
DECLARE changed_seqs text[];
DECLARE sequence_defs RECORD; c integer ;
BEGIN
FOR sequence_defs IN
select
DISTINCT(ccu.table_name) as table_name,
ccu.column_name as column_name,
replace(replace(c.column_default,'''::regclass)',''),'nextval(''','') as sequence_name
from information_schema.constraint_column_usage ccu,
information_schema.columns c
where ccu.table_schema = ANY(in_schema_name_list)
and ccu.table_schema = c.table_schema
AND c.table_name = ccu.table_name
and c.table_name like in_table_name
AND ccu.column_name = c.column_name
AND c.column_default is not null
ORDER BY sequence_name
LOOP
EXECUTE 'select max(' || sequence_defs.column_name || ') from ' || sequence_defs.table_name INTO c;
IF c is null THEN c = 1; else c = c + 1; END IF;
EXECUTE 'alter sequence ' || sequence_defs.sequence_name || ' restart with ' || c;
changed_seqs = array_append(changed_seqs, 'alter sequence ' || sequence_defs.sequence_name || ' restart with ' || c);
END LOOP;
changed_seqs = array_append(changed_seqs, 'Done');
RETURN changed_seqs;
END
$body$ LANGUAGE plpgsql;
그런 다음 실행 및 실행 중인 변경 내용을 보려면:
select *
from unnest(reset_sequences('{"django", "dbaas", "metrics", "monitor", "runner", "db_counts"}'));
돌아온다
activity_id_seq restart at 22
api_connection_info_id_seq restart at 4
api_user_id_seq restart at 1
application_contact_id_seq restart at 20
언급URL : https://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync
'programing' 카테고리의 다른 글
| Postgres DB Size 명령어 (0) | 2023.04.11 |
|---|---|
| 특정 문자열을 클립보드에 복사하기 위한 Excel VBA 코드 (0) | 2023.04.11 |
| 시뮬레이터 오류 FBSSystemServiceDomain 코드 4 (0) | 2023.04.11 |
| 자동 커밋 없이 Git 병합 (0) | 2023.04.11 |
| Bash, no-arguments 경고 및 케이스 결정 (0) | 2023.04.11 |