PostgreSQL 10+ Serial vs Identity column 비교

PostgreSQL Serial 타입과 그 문제점

Postgresql의 smallserial,serial,bigserial은 실제로 존재하는 데이터 타입은 아니며 실제로는 smallint, int, bigint를 편리하기 사용하도록 default값과 의존도(디펜던시) 그리고 오너십을 연결시켜 놓은 것임.

편하게 시퀀스를 사용할 수 있지만, 이로 인해 발생되는 부가적인 문제점들은 아래와 같음.

SQL표준에 대응하지 않으며 PostgreSQL에만 존재함으로써 발생하는 이슈가 있음(이관시)
Serial속성을 ALTER TABLE 명령어로 변경이 불가
default를 DROP하더라도 시퀀스가 DROP되지 않음
테이블 오너십 등의 변경이 필요한 경우 시퀀스에 대한 권한을 별도로 부여하여야 함(시리얼의 경우 테이블과 시퀀스가 완벽하게 연결되어 있지 않음.)

set value를 하는 방식으로 사용하고 싶은 경우, 연결된 시퀀스의 이름을 일단 찾아 별도로 변경해주어야 함.
serial 사용시 예시: ALTER SEQUENCE test_old_id_seq RESTART WITH 1000; -- 시퀀스명 알아야 함.
identity columns 사용시 예시: ALTER TABLE test_new ALTER COLUMN id RESTART WITH 1000; -- 시퀀스명 몰라도 됨.


serial에 사용되는 시퀀스가 drop이 가능함 : drop sequence가 불가능함(컬럼 먼저 변경하여야 함.)

 

 

이를 해결하기 위해 나온 것이 PostgreSQL 10버전의 Identity columns 임

serial에서 identity column으로의 업그레이드 함수를 제공함.(참고사항)

SELECT upgrade_serial_to_identity('public.test_old', 'id');

 

 

• 기존 방식(serial) 대비 테이블 구조를 복제시에도 문제가 발생하지 않게 변경됨.

-- 기존 serial 사용의 경우 
CREATE TABLE test_old2 (LIKE test_old INCLUDING ALL);
INSERT INTO test_old2 (payload) VALUES ('e') RETURNING *;
 id | payload
----+---------
  4 | e
-- serial 타입을 사용시에 해당 명령어로 테이블을 가져오면, 새로 복제된 테이블도 기존 시퀀스를 계속 참조함.
-- 이로 인해 원본 테이블을 drop하려고 하면 오류가 발생함..

-- identity column 사용의 경우
CREATE TABLE test_new2 (LIKE test_new INCLUDING ALL);
INSERT INTO test_new2 (payload) VALUES ('e') RETURNING *;
 id | payload
----+---------
  1 | e

-- 새로운 시퀀스를 생성함.

 

 

• Identity columns 명령어(PostgreSQL 10버전에서부터 지원)

-- 기본 명령어로는 update나 insert를 사용할 수 없게 할 수 있음.(serial에서는 그냥 동작함)
-- 예시:
$ insert into test_new (id, payload) values (99, 'x') returning *;
 id | payload 
----+---------
 99 | x
(1 row)
 
$ insert into test_new_2 (id, payload) values (99, 'x') returning *;
ERROR:  cannot insert into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

-- overriding system 옵션 사용하는 경우 
$ insert into test_new_2 (id, payload)
    OVERRIDING SYSTEM VALUE
    values (99, 'x')
    returning *;
 id | payload 
----+---------
 99 | x
(1 row)

--  copy 명령어에서는 동작하지 않는다는 점을 기억해두자.

 

 

 

• 시리얼을 사용하지 않고 별도로 관리하는 경우

CREATE SEQUENCE "YOURSCHEMA"."SEQNAME";

ALTER TABLE "YOURSCHEMA"."TABLENAME"
   ALTER COLUMN "COLUMNNAME" SET DEFAULT nextval('"YOURSCHEMA"."SEQNAME"'::regclass);
ALTER TABLE "YOURSCHEMA"."TABLENAME" ADD CONSTRAINT pk PRIMARY KEY ("COLUMNNAME");

관련글 :

Waiting for PostgreSQL 10 – Identity columns – select * from depesz;

PostgreSQL 10 identity columns explained - 2ndQuadrant | PostgreSQL