Serial Types 과 Identity Columns

Serial Types

smallserial, serial, bigserial data type은 실제 data type은 아님.

unique identifier column을 생성하기 위한 표기상의 편의

 

간단 예시

CREATE TABLE tablename (
    colname SERIAL
);

#### 위의 data type 선언은 아래의 구문을 수행한 것과 같음.
#### 시퀀스를 table의 특정 column에서 소유되도록 표시되므로 column이나 table이 삭제되면 시퀀스도 같이 삭제됨.
CREATE SEQUENCE tablename_colname_seq AS integer;

CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);

ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

 

identity columns

Identity Column은 PostgreSQL에서 기본 키(primary key)로 자주 사용되는 자동 증가(auto-increment) 숫자 열을 정의하는 표준 SQL 방식

이전에는 SERIAL 타입을 사용했지만, 이제는 SQL 표준을 따르는 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY 구문을 권장

 

주요 특징

 

• SERIAL과 다르게, 시퀀스 객체를 명시적으로 드러내지 않고 사용.
• 하나의 테이블에 여러 개의 identity column을 둘 수 없다.
• 기본값을 자동으로 설정해주는 시퀀스가 내부적으로 생성됨.
• 기존의 SERIAL보다 더 명시적이고 제어 가능.

CREATE TABLE users (
  id INTEGER GENERATED ALWAYS AS IDENTITY,
  name TEXT
);

 

 

GENERATED ALWAYS vs GENERATED BY DEFAULT 차이

 

• 외부에서 ID를 넣는 걸 막고 싶으면 ALWAYS,
• 유연하게 사용하고 싶으면 BY DEFAULT가 적합함.

구분	GENERATED ALWAYS AS IDENTITY	GENERATED BY DEFAULT AS IDENTITY
기본 동작	값을 항상 시퀀스로 자동 생성	시퀀스를 사용하지만 필요 시 수동 지정 가능
수동 값 입력	기본적으로 불가능 (예: INSERT에서 수동 id 값 넣으면 오류 발생)	수동 입력이 허용됨
수동 입력 허용하려면?	OVERRIDING SYSTEM VALUE를 명시해야 함	그냥 수동 값 넣으면 됨
사용 예	보안, 무결성이 중요한 경우 (외부에서 ID를 강제로 넣지 못하게)	마이그레이션 등 유연성이 필요한 경우

 

-- ALWAYS: 무조건 자동 증가
CREATE TABLE example1 (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT
);

INSERT INTO example1 (id, name) VALUES (100, 'Alice');
-- 오류 발생!

-- 수동 입력이 필요하면:
INSERT INTO example1 (id, name)
VALUES (100, 'Alice')
OVERRIDING SYSTEM VALUE;

-- BY DEFAULT: 수동 입력 가능
CREATE TABLE example2 (
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name TEXT
);

INSERT INTO example2 (id, name) VALUES (100, 'Bob');
-- 정상 수행

 

 

번외 - 시퀀스 뷰에서 조회되는지 확인해보고 싶어서 진행

• 어떤 방식을 사용하더라도 sequence view에서 조회가 된다.

CREATE TABLE t_serial (
    id SERIAL,
    val text
);

CREATE SEQUENCE t_manual_seq START 1 INCREMENT 1 MAXVALUE 100;

ALTER SEQUENCE t_manual_seq RESTART WITH 50;

select nextval('placeappo.t_manual_seq');

insert into t_serial(val) values('hi');


CREATE TABLE example1 (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT
);

INSERT INTO example1 (name) VALUES ('Alice');


select * from pg_sequences where sequencename in ('t_serial_id_seq','t_manual_seq','example1_id_seq');
 schemaname |  sequencename   | sequenceowner | data_type | start_value | min_value | max_value  | increment_by | cycle | cache_size | last_value 
------------+-----------------+---------------+-----------+-------------+-----------+------------+--------------+-------+------------+------------
 placeappo  | example1_id_seq | placeappo     | integer   |           1 |         1 | 2147483647 |            1 | f     |          1 |          1
 placeappo  | t_manual_seq    | placeappo     | bigint    |           1 |         1 |        100 |            1 | f     |          1 |         50
 placeappo  | t_serial_id_seq | placeappo     | integer   |           1 |         1 | 2147483647 |            1 | f     |          1 |          1

 

 

참고

• https://www.postgresql.org/docs/17/datatype-numeric.html#DATATYPE-SERIAL
• https://www.postgresql.org/docs/17/ddl-identity-columns.html