58강 SQL 초석 다지기 : PostgreSQL Identity Column

PostgreSQL 기초강의, PostgreSQL 기초강좌, PostgreSQL 기본강의, PostgreSQL 기본강좌

58강 SQL 초석 다지기 : PostgreSQL Identity Column   

 

SQL 초석 다지기 58강 시작합니다.

 

이번 시간에는

▶ PostgreSQL Identity Column

에 대해서 알아보는 시간입니다.

 

요약: 이번 강의에서는 GENERATED AS IDENTITY  제약 조건을 사용하여 테이블의 PostgreSQL  identity column을 생성하는 방법에 대해 알아봅니다.

---

Introduction to PostgreSQL identity column   

PostgreSQL 버전 10부터 고유 번호를 열에 자동으로 할당할 수 있는 새로운 제약 조건인 Generated AS Identity가 도입되었습니다.

Generated AS Identity 제약 조건은 기존 SERIAL 열의 SQL 표준 준수 변형입니다.

다음은 Generated AS Identity  제약 조건의 구문을 보여줍니다.

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

 

이 구문에서:

• 유형은 SMALLINT, INT 또는 BIGINT일 수 있습니다.
• Generated는 항상 PostgreSQL에 ID 열에 대한 값을 항상 생성하도록 지시합니다. GENERATED ALWAYS AS IDENTITY 열에 값을 삽입(또는 업데이트)하려고 하면 PostgreSQL에서 오류가 발생합니다.
• GENERATED BY DEFAULT 또한 PostgreSQL에 ID 열에 대한 값을 생성하도록 지시합니다. 그러나 삽입 또는 업데이트 값을 제공하면 PostgreSQL은 시스템 생성 값을 사용하는 대신 해당 값을 사용하여 ID 열에 삽입합니다.

 

PostgreSQL을 사용하면 테이블이 둘 이상의 ID 열을 가질 수 있습니다. SERIAL과 마찬가지로 GENERATED AS IDENTITY 제약 조건도 내부적으로 SEQUENCE 개체를 사용합니다.

---

PostgreSQL identity column examples   

 

A) GENERATED ALWAYS example   

 

먼저 color_id를 식별 열로 사용하여 color라는 테이블을 만듭니다.

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

 

둘째, color table에 행을 삽입합니다.

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

 

color_id 열에는 GENERATED AS IDENTITY 제약 조건이 있으므로 PostgreSQL은 아래 쿼리에 표시된 대로 이에 대한 값을 생성합니다.

SELECT * FROM color;   

 

셋째, color_id 및 color_name 열 모두에 값을 제공하여 행을 삽입합니다.

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

PostgreSQL에서 다음 오류가 발생했습니다.

오류를 수정하려면 다음과 같이 OVERRIDING SYSTEM VALUE 절을 사용할 수 있습니다.

또는  GENERATED BY DEFAULT AS IDENTITY를 사용할 수 있습니다.

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

 

다시 조회해 보면,

---

B) GENERATED BY DEFAULT AS IDENTITY example   

먼저 color table을 삭제하고 다시 만듭니다. 이번에는 GENERATED BY DEFAULT AS IDENTITY를 대신 사용합니다.

DROP TABLE color;

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

 

둘째, color table에 행을 삽입합니다.

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

 

셋째, color_id 열에 대한 값이 있는 다른 행을 삽입합니다.

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

 

GENERATED ALWAYS AS IDENTITY 제약 조건을 사용하는 이전 예제와 달리 위의 문은 완벽하게 작동합니다. 에러가 나지 않는다는 거죠.

 

SELECT * FROM color;   

---

C) Sequence options example   

GENERATED AS IDENTITY 제약 조건은 SEQUENCE 개체를 사용하므로 시스템 생성 값에 대한 시퀀스 옵션을 지정할 수 있습니다.

예를 들어 다음과 같이 시작 값과 증분을 지정할 수 있습니다.

DROP TABLE color;

CREATE TABLE color (
    color_id INT GENERATED BY DEFAULT AS IDENTITY 
    (START WITH 10 INCREMENT BY 10),
    color_name VARCHAR NOT NULL
);    

이 예에서 color_id 열의 시스템 생성 값은 10으로 시작하고 증분 값도 10입니다.

 

먼저 color table에 새 행을 삽입합니다. 

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

 

color_id 열의 시작 값은 아래와 같이 10입니다.

SELECT * FROM color;   

 

둘째, color table에 다른 행을 삽입합니다.

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

 

두 번째 행의 color_id 값은 increment 옵션 때문에 20입니다.

SELECT * FROM color;      

---

Adding an identity column to an existing table   

다음 형식의 ALTER TABLE 문을 사용하여 기존 테이블에 ID 열을 추가할 수 있습니다.

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

 

다음 예를 만들어 보겠습니다.

먼저 shape라는 새 테이블을 만듭니다.

CREATE TABLE shape (
    shape_id INT NOT NULL,
    shape_name VARCHAR NOT NULL
);   

 

둘째, shape_id 열을 identity 열로 변경합니다.

ALTER TABLE shape 
ALTER COLUMN shape_id ADD GENERATED ALWAYS AS IDENTITY;   

 

identity 칼럼으로 변경하기 위해서는 not null 제약조건이 있어야 합니다.

not null 제약조건이 없는 칼럼을 변경하면 에러가 납니다.

 

다음 명령은 psql 도구에서 shape table을 설명합니다.

\d shape   

---

Changing an identity column   

다음 ALTER TABLE 문을 사용하여 기존 ID 열의 특성을 변경할 수 있습니다.

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

 

예를 들어 다음 문은 shape 테이블의 shape_id 열을 GENERATED BY DEFAULT로 변경합니다.

ALTER TABLE shape
ALTER COLUMN shape_id SET GENERATED BY DEFAULT;   

 

다음 명령은 psql 도구에서 shape table을 설명합니다.

\d shape   

출력에서 볼 수 있듯이 shape_id 열이 GENERATED ALWAYS에서 GENERATED BY DEFAULT로 변경되었습니다.

---

Removing the GENERATED AS IDENTITY constraint   

다음 문은 기존 테이블에서 GENERATED AS IDENTITY 제약 조건을 제거합니다.

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

 

예를 들어 다음과 같이 shape 테이블의 shape_id 열에서 GENERATED AS IDENTITY 제약 조건 열을 제거할 수 있습니다.

ALTER TABLE shape
ALTER COLUMN shape_id
DROP IDENTITY IF EXISTS;   

 

다음 명령은 psql 도구에서 shape table을 설명합니다.

\d shape   

출력에서 볼 수 있듯이 shape_id 열의 제약조건이 없어졌습니다.

---

이본 시간에는 PostgreSQL IDENTITY 열을 사용하는 방법과 GENERATED AS IDENTITY 제약 조건을 사용하여 이를 관리하는 방법을 배웠습니다.

 

수고하셨습니다.

---

본 강의는 PostgreSQL Tutorial을 참조합니다.  https://www.postgresqltutorial.com/

Do it! SQL을 찾아 주셔서 감사합니다. 공감 ♥ , 댓글이 큰 힘이 됩니다.