Postgres의 기존 열에 'serial' 추가

Postgres의 기존 열에 'serial' 추가

Postgres 9.0 데이터베이스에 정수 ID 필드(기본 키)가 있는 작은 테이블(~30개 행)이 있습니다. 이 필드에는 현재 1로 시작하는 고유한 순차 정수가 포함되어 있지만 'serial' 키워드를 사용하여 작성되지 않았습니다.

이제부터 이 테이블에 삽입하면 이 필드가 'serial'을 유형으로 사용하여 작성된 것처럼 동작하도록 이 테이블을 변경하려면 어떻게 해야 합니까?

다음 명령(특히 주석이 달린 블록)을 확인합니다.

DROP TABLE foo;
DROP TABLE bar;

CREATE TABLE foo (a int, b text);
CREATE TABLE bar (a serial, b text);

INSERT INTO foo (a, b) SELECT i, 'foo ' || i::text FROM generate_series(1, 5) i;
INSERT INTO bar (b) SELECT 'bar ' || i::text FROM generate_series(1, 5) i;

-- blocks of commands to turn foo into bar
CREATE SEQUENCE foo_a_seq;
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq');
ALTER TABLE foo ALTER COLUMN a SET NOT NULL;
ALTER SEQUENCE foo_a_seq OWNED BY foo.a;    -- 8.2 or later

SELECT MAX(a) FROM foo;
SELECT setval('foo_a_seq', 5);  -- replace 5 by SELECT MAX result

INSERT INTO foo (b) VALUES('teste');
INSERT INTO bar (b) VALUES('teste');

SELECT * FROM foo;
SELECT * FROM bar;

사용할 수도 있습니다.START WITH특정 지점에서 시퀀스를 시작하는 것, 비록 세트발이 오일러의 답에서와 같은 것을 달성하지만, 예를 들어,

SELECT MAX(a) + 1 FROM foo;
CREATE SEQUENCE foo_a_seq START WITH 12345; -- replace 12345 with max above
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq');

TL;DR

여기 사람이 값을 읽고 직접 입력할 필요가 없는 버전이 있습니다.

CREATE SEQUENCE foo_a_seq OWNED BY foo.a;
SELECT setval('foo_a_seq', coalesce(max(a), 0) + 1, false) FROM foo;
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq'); 

또 다른 옵션은 재사용 가능한 시스템을 사용하는 것입니다.Function이 답변의 끝에 공유되었습니다.

---

대화형 솔루션이 아닌 솔루션

다른 두 개의 답을 더하면, 이것들을 가져야 하는 우리들을 위한 것입니다.Sequences는 대화형이 아닌 스크립트에 의해 생성되며 라이브 상태의 DB를 패치합니다.

네가 원하지 않을 때SELECT값을 수동으로 입력하고 다음에 직접 입력합니다.CREATE진술.

간단히 말해서, 다음을 수행할 수 없습니다.

CREATE SEQUENCE foo_a_seq
    START WITH ( SELECT max(a) + 1 FROM foo );

이래로START [WITH]의 조항.CREATE SEQUENCE하위 쿼리가 아닌 값을 예상합니다.

참고: 경험에 비추어 볼 때, CRUD가 아닌 모든 것(즉, 다음 이외의 모든 것)에 적용됩니다.INSERT,SELECT,UPDATE,DELETEpgSQL AFAIK의 문입니다.

하지만,setval()합니다! 따라서 다음은 절대적으로 문제가 없습니다.

SELECT setval('foo_a_seq', max(a)) FROM foo;

데이터가 없고 데이터에 대해 모르는 경우(원하지 않는 경우)coalesce()기본값을 설정하려면:

SELECT setval('foo_a_seq', coalesce(max(a), 0)) FROM foo;
--                         ^      ^         ^
--                       defaults to:       0

그러나 현재 시퀀스 값을 다음으로 설정0불법은 아니더라도 어설프다.
다음의 3-매개 변수 형식 사용setval더 적합할 것입니다.

--                                             vvv
SELECT setval('foo_a_seq', coalesce(max(a), 0) + 1, false) FROM foo;
--                                                  ^   ^
--                                                is_called

선택적인 세 번째 파라미터 설정setval로.false다음을 막을 것입니다.nextval값을 반환하기 전에 시퀀스를 진행하여 다음과 같이 처리합니다.

다음.nextval정확하게 지정된 값을 반환하고, 시퀀스 진행은 다음과 함께 시작됩니다.nextval.

문서의 이 항목에서.

관련이 없는 노트에서는 다음을 소유하는 열을 지정할 수도 있습니다.Sequence와 직접적으로CREATE나중에 변경할 필요가 없습니다.

CREATE SEQUENCE foo_a_seq OWNED BY foo.a;

요약:

CREATE SEQUENCE foo_a_seq OWNED BY foo.a;
SELECT setval('foo_a_seq', coalesce(max(a), 0) + 1, false) FROM foo;
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq'); 

---

사용Function

또는 여러 열에 대해 이 작업을 수행할 계획인 경우 실제 열을 사용하는 방법을 선택할 수 있습니다.Function.

CREATE OR REPLACE FUNCTION make_into_serial(table_name TEXT, column_name TEXT) RETURNS INTEGER AS $$
DECLARE
    start_with INTEGER;
    sequence_name TEXT;
BEGIN
    sequence_name := table_name || '_' || column_name || '_seq';
    EXECUTE 'SELECT coalesce(max(' || column_name || '), 0) + 1 FROM ' || table_name
            INTO start_with;
    EXECUTE 'CREATE SEQUENCE ' || sequence_name ||
            ' START WITH ' || start_with ||
            ' OWNED BY ' || table_name || '.' || column_name;
    EXECUTE 'ALTER TABLE ' || table_name || ' ALTER COLUMN ' || column_name ||
            ' SET DEFAULT nextVal(''' || sequence_name || ''')';
    RETURN start_with;
END;
$$ LANGUAGE plpgsql VOLATILE;

다음과 같이 사용합니다.

INSERT INTO foo (data) VALUES ('asdf');
-- ERROR: null value in column "a" violates not-null constraint

SELECT make_into_serial('foo', 'a');
INSERT INTO foo (data) VALUES ('asdf');
-- OK: 1 row(s) affected

PostgreSQL 10 이상

사용하지 마십시오.위의 코멘트 덕분에.

ALTER TABLE mysmalltable
ALTER COLUMN id
ADD GENERATED BY DEFAULT AS IDENTITY;

나를위일것는하해것에서 저를 .PostgreSQL 12.

기존의 int 열을 직렬 열로 변환합니다.

CREATE SEQUENCE table_name_id_seq;
ALTER TABLE table_name ALTER COLUMN id SET DEFAULT nextval('table_name_id_seq');
ALTER TABLE table_name ALTER COLUMN id SET NOT NULL;
ALTER SEQUENCE table_name_id_seq OWNED BY table_name.id;  
SELECT setval('table_name_id_seq', (SELECT max(id) FROM table_name));

DBMS를 사용하는 경우 열의 기본값을 Sequence로 설정할 수 있습니다.

고급 옵션이 있을 수 있습니다.

언급URL : https://stackoverflow.com/questions/9490014/adding-serial-to-existing-column-in-postgres