PostgreSQLで自動採番をするシーケンス(sequence)とは【AUTO INCREMENT】

PostgreSQLでは、INSERTされるプライマリキーはSERIAL型を指定していた場合、シーケンス(sequence)により管理されています。

シーケンスを利用したINSERT
現在のシーケンス値を取得する
 idを直接指定した場合はシーケンスとのずれが発生する
 プライマリキーとシーケンスを同期させるには
 テーブル作成時にシーケンスを作成するには
 データ型
 テーブル作成後にシーケンスを追加するには
 シーケンスの最大値に到達したときの挙動
 シーケンスのサイズを変更するには
 シーケンス関数
 [参考記事] ERROR: duplicate key value violates unique constraint "hoge_pkey"と出る場合

シーケンスを利用したINSERT

シーケンスにはidの現在値が保存されており、nextval()により新しい値が取得されます。

idにnextval()の値を指定してINSERTを行います。

INSERT INTO users (id, name) VALUES (nextval('users_id_seq'), 'hoge');

idを指定しなかった場合は、idにnextval()が指定されたものとしてINSERTされます。

INSERT INTO users (name) VALUES ('hoge');
は
INSERT INTO users (id, name) VALUES (nextval('users_id_seq'), 'hoge');
と同じです。

nextval()が実行された時点で、シーケンスは新しい値に進められるため、INSERTでエラーが発生した場合はidの欠番ができます。
欠番が出ないようにするにはトランザクションを使用します。

現在のシーケンス値を取得する

現在のシーケンス値を取得するには、currval()やlast_valueを使用します。

SELECT currval('users_id_seq');
SELECT last_value FROM users_id_seq;

currval()とlast_valueは、ともに現在のシーケンス値が返りますが、シーケンスに値がセットされていない時の挙動が異なります。

currval()ではエラーが発生します。

# SELECT currval('users_id_seq');
ERROR:  currval of sequence "users_id_seq" is not yet defined in this session

last_valueでは、値がセットされていない時に0ではなく1が返ります。

# SELECT last_value FROM users_id_seq;
 last_value
------------
          1
(1 row)

idを直接指定した場合はシーケンスとのずれが発生する

テーブルのプライマリキーとシーケンスは連動していないため、nextval()を使用せずidを直接指定した場合はシーケンスとのずれが発生することがあります。

# SELECT currval('users_id_seq');
 currval
---------
       10
(1 row)

# INSERT INTO users (id, name) VALUES (11, 'hoge');
INSERT 0 1

# SELECT currval('users_id_seq');
 currval
---------
       10
(1 row)

# INSERT INTO users (name) VALUES ('hoge');
ERROR:  duplicate key value violates unique constraint "users_pkey"
DETAIL:  Key (id)=(11) already exists.

プライマリキーとシーケンスを同期させるには

プライマリキーとシーケンスを同期させるには、次のようにします。

SELECT SETVAL ('users_id_seq', (SELECT MAX(id) FROM users));

これはシーケンスに値をセットするSETVALと、テーブルIDの最大値を得るMAXを組み合わせたものです。

シーケンスに値をセットする
SELECT SETVAL ('シーケンス名', 数値);

テーブルidの最大値を得る
SELECT MAX(id) FROM users;

idの値を指定してINSERTしたときには、プライマリキーとシーケンスを同期させるようにしたほうがよいです。

テーブル作成時にシーケンスを作成するには

テーブル作成時にシーケンスを作成するには、idにSERIAL型を指定します。

create table users (
  id serial,
  name character varying(128),
  primary key (id)
);

SERIAL型は2,147,483,647までの値を扱うことができるため、それ以上の値を扱いたい場合はBIGSERIAL型を指定します。
またSERIAL型は4バイトを使用するため、データ量を少なくしたい場合は32,767までを扱えるSMALLSERIAL型を指定します。

データ型

型名	格納サイズ	説明	範囲
smallserial	2バイト	狭範囲自動整数	1から32767 (5桁)
serial	4バイト	自動増分整数	1から2147483647 (10桁)
bigserial	8バイト	広範囲自動増分整数	1から9223372036854775807 (19桁)

テーブル作成後にシーケンスを追加するには

SERIAL型を指定してテーブルを作成したときには、実際には次のようにINTEGER型に変換されシーケンスが作成されます。

create table users (
  id integer NOT NULL,
  name character varying(128),
  primary key (id)
);

CREATE SEQUENCE users_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE users_id_seq OWNED BY users.id;

smallserial	→	smallint
serial	→	integer
bigserial	→	bigint

テーブル作成後にシーケンスを追加するには、CREATE SEQUENCEでシーケンスを作成し、ALTER SEQUENCEでテーブルと連携します。

シーケンスの最大値に到達したときの挙動

シーケンスにCYCLEを指定してない場合は最大値になるとnextvalでreached maximum valueエラーが出ます。
CYCLEを指定していると1に戻りますが、テーブルのidに重複が発生します。

SELECT SETVAL ('users_id_seq', 2147483647);

# SELECT nextval('users_id_seq');
ERROR:  nextval: reached maximum value of sequence "users_id_seq" (2147483647)

シーケンスのサイズを変更するには

シーケンスのサイズを変更する場合は、SERIAL型を指定するのではなく、テーブル・シーケンスのそれぞれでINTEGER型のサイズを変更します。
これはSERIAL型を指定してテーブルを作成したときに、実際にはINTEGER型に変換され対応するシーケンスが作成されているからです。

ALTER TABLE users ALTER COLUMN id TYPE bigint;
ALTER SEQUENCE users_id_seq AS bigint;

シーケンス関数

関数	戻り値	説明
currval(regclass)	bigint	指定されたシーケンスに対しnextvalで得られた最新の値を返す
lastval()	bigint	すべてのシーケンスに対してnextvalにより最も最近取得された値を返す
nextval(regclass)	bigint	シーケンスを進め、新しい値を返す
setval(regclass, bigint)	bigint	シーケンスの現在値を設定する
setval(regclass, bigint, boolean)	bigint	シーケンスの現在値とis_calledフラグを設定する