[PostgreSQL] BASIC - Identity Column

 

GENERATED AS IDENTITY제약 조건을 사용하여 테이블에 대한 PostgreSQL ID 열을 생성하는 방법

 

GENERATED AS IDENTITY에는 열에 고유 번호를 자동으로 할당할 수 있는 새로운 제약 조건이 있습니다.

column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]

---

CREATE TABLE color (
    color_id INT GENERATED ALWAYS AS IDENTITY,
    color_name VARCHAR NOT NULL
);

INSERT INTO color(color_name)
VALUES ('Red');

SELECT * FROM color;

INSERT INTO color (color_id, color_name)
VALUES (2, 'Green');

오류 발생

[Err] ERROR: cannot insert into column "color_id" DETAIL: Column "color_id" is an identity column defined as GENERATED ALWAYS. HINT: Use OVERRIDING SYSTEM VALUE to override.

해결 :

INSERT INTO color (color_id, color_name)
OVERRIDING SYSTEM VALUE 
VALUES(2, 'Green');

-- or
-- GENERATED BY DEFAULT AS IDENTITY

 

 

기존 테이블에 ID열 추가

ALTER TABLE table_name 
ALTER COLUMN column_name 
ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY { ( sequence_option ) }

 

ID열 변경

ALTER TABLE table_name 
ALTER COLUMN column_name 
{ SET GENERATED { ALWAYS| BY DEFAULT } | 
  SET sequence_option | RESTART [ [ WITH ] restart ] }

 

제약 조건 제거

ALTER TABLE table_name 
ALTER COLUMN column_name 
DROP IDENTITY [ IF EXISTS ]