postgres에서 시퀀스를 재설정하고 ID 열을 새 데이터로 채우는 방법은 무엇입니까?

postgres에서 시퀀스를 재설정하고 ID 열을 새 데이터로 채우는 방법은 무엇입니까?

저는 100만 줄이 넘는 테이블을 가지고 있습니다.시퀀스를 재설정하고 새 값(1, 2, 3, 4 등)으로 id 열을 재할당해야 합니다.그렇게 하는 쉬운 방법이 있습니까?

ID의 순서를 유지하지 않으려면 다음과 같이 하십시오.

ALTER SEQUENCE seq RESTART WITH 1;
UPDATE t SET idcolumn=nextval('seq');

테이블 전체를 다시 만들지 않고 선택한 순서대로 쉽게 할 수 있는 방법이 있는지 의심스럽습니다.

포함SQL 8 이상을 사용하면 SQL 8.4 또버은다지를 .WITH 1더상이기. 의에시로 기록된 .CREATE SEQUENCE또는 마지막 설정자ALTER SEQUENCE START WITH사용될 것입니다(아마도 이것은 1일 것입니다).

시퀀스를 재설정합니다.

ALTER SEQUENCE seq RESTART;

그런 다음 테이블의 ID 열을 업데이트합니다.

UPDATE foo SET id = DEFAULT;

출처: PostgreSQL 문서

시퀀스를 재설정합니다.

SELECT setval('sequence_name', 0);

현재 레코드 업데이트 중:

UPDATE foo SET id = DEFAULT;

1번부터 다시 시작하도록 시퀀스를 재설정하는 가장 좋은 방법은 다음을 실행하는 것입니다.

ALTER SEQUENCE <tablename>_<id>_seq RESTART WITH 1

예를 들어 사용자 테이블의 경우 다음과 같습니다.

ALTER SEQUENCE users_id_seq RESTART WITH 1

시퀀스를 재설정하기 위한 ALTER SEQUENCE 및 SELECT Setval의 적절한 사용을 단순화하고 명확히 하기 위해서만:

ALTER SEQUENCE sequence_name RESTART WITH 1;

와 동등합니다.

SELECT setval('sequence_name', 1, FALSE);

두 문 중 하나를 사용하여 시퀀스를 재설정할 수 있으며, 다음과 같이 nextval('sequence_name')로 다음 값을 얻을 수 있습니다.

nextval('sequence_name')

제공된 두 가지 솔루션 모두 저에게는 효과가 없었습니다.

> SELECT setval('seq', 0);
ERROR:  setval: value 0 is out of bounds for sequence "seq" (1..9223372036854775807)

setval('seq', 1)2로 하고, 번호 매기기는 2로 시작합니다.ALTER SEQUENCE seq START 1.is seq.is _called true(Postgres 버전 9.0.4)이기 때문에 합니다.

효과적인 솔루션은 다음과 같습니다.

> ALTER SEQUENCE seq RESTART WITH 1;
> UPDATE foo SET id = DEFAULT;

행 순서를 유지하는 방법

UPDATE thetable SET rowid=col_serial FROM 
(SELECT rowid, row_number() OVER ( ORDER BY lngid) AS col_serial FROM thetable ORDER BY lngid) AS t1 
WHERE thetable.rowid=t1.rowid;

SELECT SETVAL('seq_my_table_pk_id', (SELECT MAX(my_table_pk_id) + 1 FROM my_table));

참고: ID 범위(예: 256 - 10000000) 사이에 새 시작 값을 지정해야 하는 경우:

SELECT setval('"Sequence_Name"', 
       (SELECT coalesce(MAX("ID"),255) 
           FROM "Table_Name" 
           WHERE "ID" < 10000000 and "ID" >= 256)+1
       ); 

시퀀스를 재설정하고 모든 행을 업데이트하는 것만으로도 중복 ID 오류가 발생할 수 있습니다.대부분의 경우 모든 행을 두 번 업데이트해야 합니다.먼저 중복을 방지하기 위해 ID가 높은 경우, 그 다음에 실제로 원하는 ID를 사용합니다.

모든 ID에 고정 금액을 추가하지 마십시오(다른 의견에서 권장됨).이 고정된 양보다 더 많은 행이 있으면 어떻게 됩니까?시퀀스의 다음 값이 기존 행의 모든 id보다 높다고 가정하면(단, 공백을 채우려는 경우), 다음과 같이 수행합니다.

UPDATE table SET id = DEFAULT;
ALTER SEQUENCE seq RESTART;
UPDATE table SET id = DEFAULT;

저의 경우 다음을 통해 이를 달성했습니다.

ALTER SEQUENCE table_tabl_id_seq RESTART WITH 6;

내 테이블이 테이블로 명명된 위치

예를 들어, "FIELD_"의 최대값을 사용하여 시퀀스 "SEQ_A"의 값을 업데이트하려면테이블 "TAB_B"의 ID 필드에서 다음 명령을 사용할 수 있습니다.

SELECT setval('SEQ_A', (SELECT max(FIELD_ID) FROM TAB_B));

이 명령은 "FIELD_"의 최대값을 선택합니다.테이블 "TAB_B"의 ID 필드를 시퀀스 "SEQ_A"의 다음 값으로 설정합니다.

나의 경우 잘못된 sql 파일을 가져온 후 모든 테이블의 시퀀스가 손상되었습니다. SELECT nextval('table_name_id_seq');는 의 값 보 값 반 습 했 니 다 환 을 작 최 은 대 다 ▁value ▁less ▁of ▁max ▁than ▁the ▁returning ▁was 다 니 습 했 의id 각 를 복구했습니다그래서 각 테이블의 모든 시퀀스를 복구하기 위해 sql 스크립트를 만들었습니다.

DO
$$
DECLARE
   rec  record;
   table_seq text;
BEGIN
   FOR rec IN
      SELECT *
      FROM   pg_tables
      WHERE  tablename NOT LIKE 'pg\_%'
      ORDER  BY tablename
   LOOP
      table_seq := rec.tablename || '_id_seq';
     
      RAISE NOTICE '%', table_seq;
      EXECUTE format(E'SELECT setval(\'%I\', COALESCE((SELECT MAX(id)+1 FROM %I), 1), false);',
            table_seq, rec.tablename);
      
   END LOOP;
END
$$;

참고: 만약 당신이 가지고 있지 않습니다.id테이블의 열을 사용하면 논리를 업데이트하거나 위의 논리를 기반으로 개별적으로 처리할 수 있습니다.

pgAdmin3을 사용하는 경우 'Sequences'를 확장하고 시퀀스를 마우스 오른쪽 단추로 클릭한 다음 'Properties'로 이동한 다음 'Definition' 탭에서 'Current value'를 원하는 값으로 변경합니다.쿼리가 필요하지 않습니다.

여기 있는 다른 답변에서 영감을 받아 시퀀스 마이그레이션을 수행하는 SQL 함수를 만들었습니다.함수는 기본 키 시퀀스를 기존 시퀀스 범위 내 또는 외부의 값(>= 1)으로 시작하는 새 연속 시퀀스로 이동합니다.

스키마는 같지만 값이 다른 두 데이터베이스를 하나의 데이터베이스로 마이그레이션할 때 이 기능을 어떻게 사용했는지 설명합니다.

첫째, 함수(생성된 SQL 명령을 인쇄하여 실제로 무슨 일이 일어나고 있는지 명확하게 함):

CREATE OR REPLACE FUNCTION migrate_pkey_sequence
  ( arg_table      text
  , arg_column     text
  , arg_sequence   text
  , arg_next_value bigint  -- Must be >= 1
  )
RETURNS int AS $$
DECLARE
  result int;
  curr_value bigint = arg_next_value - 1;
  update_column1 text := format
    ( 'UPDATE %I SET %I = nextval(%L) + %s'
    , arg_table
    , arg_column
    , arg_sequence
    , curr_value
    );
  alter_sequence text := format
    ( 'ALTER SEQUENCE %I RESTART WITH %s'
    , arg_sequence
    , arg_next_value
    );
  update_column2 text := format
    ( 'UPDATE %I SET %I = DEFAULT'
    , arg_table
    , arg_column
    );
  select_max_column text := format
    ( 'SELECT coalesce(max(%I), %s) + 1 AS nextval FROM %I'
    , arg_column
    , curr_value
    , arg_table
    );
BEGIN
  -- Print the SQL command before executing it.
  RAISE INFO '%', update_column1;
  EXECUTE update_column1;
  RAISE INFO '%', alter_sequence;
  EXECUTE alter_sequence;
  RAISE INFO '%', update_column2;
  EXECUTE update_column2;
  EXECUTE select_max_column INTO result;
  RETURN result;
END $$ LANGUAGE plpgsql;

수migrate_pkey_sequence에서는 다음 인수를 사용합니다.

1. arg_table 이름 테이이예름블예()'example')
2. arg_column키열 이름 기키 열예이름본예(름:▁(이▁name▁key▁primary▁column열e)'id')
3. arg_sequencename : 예름이스시퀀예(:름)'example_id_seq')
4. arg_next_value 후 은 다음과 같습니다.

다음 작업을 수행합니다.

1. 기본 키 값을 자유 범위로 이동합니다. 마아일 것입니다.nextval('example_id_seq')뒤르는에 max(id)순서가 1로 시작한다는 것입니다.이것은 또한 다음과 같은 경우를 처리합니다.arg_next_value > max(id).
2. 키 을 다으로시기본키값인이범동다니로 합니다.arg_next_value키 값의 순서는 보존되지만 범위의 구멍은 보존되지 않습니다.
3. 순서 뒤에 오는 다음 값을 인쇄합니다.다른 테이블의 열을 마이그레이션하고 이 테이블과 병합하려는 경우 유용합니다.

다음과 다과같이정예시표시사연다니합여하용를와스퀀음의된예▁defined(:▁to다니시연합▁and▁we▁sequence▁a▁tableate,e:psql):

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

# CREATE TABLE example
  ( id bigint NOT NULL DEFAULT nextval('example_id_seq'::regclass)
  );

그런 다음 다음 몇 가지 값을 삽입합니다(예: 3부터 시작).

# ALTER SEQUENCE example_id_seq RESTART WITH 3;
# INSERT INTO example VALUES (DEFAULT), (DEFAULT), (DEFAULT);
-- id: 3, 4, 5

마지막으로, 우리는 마이그레이션합니다.example.id값을 입력합니다.

# SELECT migrate_pkey_sequence('example', 'id', 'example_id_seq', 1);
INFO:  00000: UPDATE example SET id = nextval('example_id_seq') + 0
INFO:  00000: ALTER SEQUENCE example_id_seq RESTART WITH 1
INFO:  00000: UPDATE example SET id = DEFAULT
 migrate_pkey_sequence
-----------------------
                     4
(1 row)

결과:

# SELECT * FROM example;
 id
----
  1
  2
  3
(3 rows)

자동 증분 열이 PK가 아닌 경우에도(이 예에서는 seq -aka sequence라고 함) 트리거를 사용하여 이를 달성할 수 있습니다.

devops_guide 캐스케이드가 존재하는 경우 드롭 테이블;

SELECT 'create the "devops_guide" table'
;
   CREATE TABLE devops_guide (
      guid           UUID NOT NULL DEFAULT gen_random_uuid()
    , level          integer NULL
    , seq            integer NOT NULL DEFAULT 1
    , name           varchar (200) NOT NULL DEFAULT 'name ...'
    , description    text NULL
    , CONSTRAINT pk_devops_guide_guid PRIMARY KEY (guid)
    ) WITH (
      OIDS=FALSE
    );

-- START trg_devops_guide_set_all_seq
CREATE OR REPLACE FUNCTION fnc_devops_guide_set_all_seq()
    RETURNS TRIGGER
    AS $$
       BEGIN
         UPDATE devops_guide SET seq=col_serial FROM
         (SELECT guid, row_number() OVER ( ORDER BY seq) AS col_serial FROM devops_guide ORDER BY seq) AS tmp_devops_guide
         WHERE devops_guide.guid=tmp_devops_guide.guid;

         RETURN NEW;
       END;
    $$ LANGUAGE plpgsql;

 CREATE TRIGGER trg_devops_guide_set_all_seq
  AFTER UPDATE OR DELETE ON devops_guide
  FOR EACH ROW
  WHEN (pg_trigger_depth() < 1)
  EXECUTE PROCEDURE fnc_devops_guide_set_all_seq();

언급URL : https://stackoverflow.com/questions/4678110/how-to-reset-sequence-in-postgres-and-fill-id-column-with-new-data