LoginSignup
1
0

More than 3 years have passed since last update.

@KAZAMAI_NaruTo(NaruTo KAZAMAI)

PostgreSQL の Identity列に紐づく Sequence名

Posted at

PostgreSQL の Identity列

PostgreSQL は 10 から Identity列というのが追加されている。

CREATE TABLE table1(column1 int GENERATED BY DEFAULT AS identity);

上記のように GENERATED を用いることで値を指定せずに順に設定していくようになる。
12

naruto=# CREATE TABLE table1(column1 int GENERATED BY DEFAULT AS identity);
naruto=# INSERT INTO table1 DEFAULT VALUES RETURNING column1;
 column1
---------
       1
(1 row)

INSERT 0 1
naruto=# INSERT INTO table1 DEFAULT VALUES RETURNING column1;
 column1
---------
       2
(1 row)

GENERATED の後には BY DEFAULT またはALWAYS を指定する。

BY DEFAULT の場合は値を設定することも可能だが、
ALWAYS の場合は自動生成される値しか設定できない3。ただし、OVERRIDING SYSTEM VALUE句を用いれば無理やり設定はできる。

naruto=# CREATE TABLE table2(column1 int GENERATED BY DEFAULT AS identity);
CREATE TABLE
naruto=# INSERT INTO table2(column1) VALUES(2);
INSERT 0 1
naruto=# INSERT INTO table2 DEFAULT VALUES;
INSERT 0 1
naruto=# INSERT INTO table2 DEFAULT VALUES;
INSERT 0 1
naruto=# SELECT * FROM table2;
 column1
---------
       2
       1
       2
(3 rows)

naruto=# CREATE TABLE table3(column1 int GENERATED ALWAYS AS identity);
CREATE TABLE
naruto=# INSERT INTO table3(column1) VALUES(2);
ERROR:  cannot insert into column "column1"
DETAIL:  Column "column1" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
naruto=# INSERT INTO table3(column1) VALUES(DEFAULT);
INSERT 0 1
naruto=# INSERT INTO table3 DEFAULT VALUES;
INSERT 0 1
naruto=# INSERT INTO table3(column1) OVERRIDING SYSTEM VALUE VALUES(1);
INSERT 0 1
naruto=# SELECT * FROM table3;
 column1
---------
       1
       2
       1
(3 rows)

上記の例では重複した値を設定しているが、Identity列に Primary Key制約や Unique制約を設定することはよくあり、その場合に重複した値を設定しようとするとエラーとなる。

naruto=# CREATE TABLE table4(column1 int GENERATED BY DEFAULT AS identity CONSTRAINT PK_table4 PRIMARY KEY);
CREATE TABLE
naruto=# INSERT INTO table4 VALUES(2);
INSERT 0 1
naruto=# INSERT INTO table4 DEFAULT VALUES;
INSERT 0 1
naruto=# INSERT INTO table4 DEFAULT VALUES;
ERROR:  duplicate key value violates unique constraint "pk_table4"
DETAIL:  Key (column1)=(2) already exists.
naruto=# INSERT INTO table4 DEFAULT VALUES;
INSERT 0 1
naruto=# SELECT * FROM table4;
 column1
---------
       2
       1
       3
(3 rows)

identity列に紐づくsequence

PostgreSQL の Identity列に挿入される値を管理する Sequence が存在する。
通常、この Sequence の名称は　{table名}_{column名}_seq である。
ただし、「通常」と付けたとおり、そうならない場合もある。

naruto=# CREATE TABLE table5(column1 INT GENERATED BY DEFAULT AS IDENTITY CONSTRAINT PK_table5 PRIMARY KEY);
CREATE TABLE
naruto=# SELECT C.oid, C.relname, C.relkind FROM PG_CLASS C
naruto-# WHERE
naruto-#     C.relname LIKE 'table5%'
naruto-# ;
  oid  |      relname       | relkind
-------+--------------------+---------
 16484 | table5             | r
 16482 | table5_column1_seq | S
(2 rows)

naruto=# SELECT CURRVAL('table5_column1_seq');
ERROR:  currval of sequence "table5_column1_seq" is not yet defined in this session
naruto=# INSERT INTO table5 DEFAULT VALUES;
INSERT 0 1
naruto=# SELECT CURRVAL('table5_column1_seq');
 currval
---------
       1
(1 row)

naruto=# INSERT INTO table5 DEFAULT VALUES;
INSERT 0 1
naruto=# SELECT CURRVAL('table5_column1_seq');
 currval
---------
       2
(1 row)

naruto=# SELECT NEXTVAL('table5_column1_seq');
 nextval
---------
       3
(1 row)

naruto=# INSERT INTO table5 DEFAULT VALUES;
INSERT 0 1
naruto=# SELECT * FROM table5;
 column1
---------
       1
       2
       4
(3 rows)

Identity の値の設定

Identity列で次に入れるレコードの値を指定したい場合がある。
例えば、Identity列の値が設定していあるレコードがある場合など。

naruto=# CREATE TABLE table6(
naruto(#     column1 int GENERATED BY DEFAULT AS IDENTITY CONSTRAINT PK_table6 PRIMARY KEY
naruto(#     , column3 int
naruto(# );
CREATE TABLE
naruto=# INSERT INTO table6(column3) VALUES(301),(302),(303);
INSERT 0 3
naruto=# SELECT * FROM table6;
 column1 | column3
---------+---------
       1 |     301
       2 |     302
       3 |     303
(3 rows)

naruto=# CREATE TABLE table6old AS SELECT * FROM table6;
SELECT 3
naruto=# DROP TABLE table6;
DROP TABLE
naruto=# CREATE TABLE table6(
naruto(#     column1 int GENERATED BY DEFAULT AS IDENTITY CONSTRAINT PK_table6 PRIMARY KEY
naruto(#     , column2 text
naruto(#     , column3 int
naruto(# );
CREATE TABLE
naruto=# INSERT INTO table6(column1, column3) SELECT * FROM table6old;
INSERT 0 3
naruto=# DROP TABLE table6old;
DROP TABLE
naruto=# SELECT * FROM table6;
 column1 | column2 | column3
---------+---------+---------
       1 |         |     301
       2 |         |     302
       3 |         |     303
(3 rows)

naruto=# INSERT INTO table6 (column2, column3) VALUES ('Hoge', 300);
ERROR:  duplicate key value violates unique constraint "pk_table6"
DETAIL:  Key (column1)=(1) already exists.

Identity の次の値を設定するには ALTER TABLE 文を使えばできる。
ただし、この場合は DML文 と組み合わせることはできない。

naruto=# ALTER TABLE table6 ALTER COLUMN1 RESTART 4;
ALTER TABLE
naruto=# INSERT INTO table6 (column2, column3) VALUES ('Hoge', 304);
INSERT 0 1
naruto=# SELECT * FROM table6;
 column1 | column2 | column3
---------+---------+---------
       1 |         |     301
       2 |         |     302
       3 |         |     303
       4 | Hoge    |     304
(4 rows)

naruto=# ALTER TABLE table6 ALTER COLUMN1 RESTART 105;
ALTER TABLE
naruto=# INSERT INTO table6 (column2, column3) VALUES ('Hoge', 315);
INSERT 0 1
naruto=# SELECT * FROM table6;
 column1 | column2 | column3
---------+---------+---------
       1 |         |     301
       2 |         |     302
       3 |         |     303
       4 | Hoge    |     304
     105 | Hoge    |     315
(5 rows)

naruto=# INSERT INTO table6 (column1, column2, column3) VALUES (206, 'Hoge', 326);
INSERT 0 1
naruto=# ALTER TABLE table6 ALTER COLUMN1 RESTART (SELECT MAX(column1) + 1 FROM table6);
ERROR:  syntax error at or near "("
LINE 1: ALTER TABLE table6 ALTER COLUMN1 RESTART (SELECT MAX(column1...

DML文からの値を直接設定したい場合は Identity列に紐づく Sequence に対して serval を用いて直接操作することで可能となる。

naruto=# SELECT setval('table6_column1_seq',
naruto(#     (SELECT MAX(column1) + 1 FROM table6), FALSE);
 setval
--------
    207
(1 row)

naruto=# INSERT INTO table6 (column2, column3) VALUES ('Hoge', 327);
INSERT 0 1
naruto=# SELECT * FROM table6;
 column1 | column2 | column3
---------+---------+---------
       1 |         |     301
       2 |         |     302
       3 |         |     303
       4 | Hoge    |     304
     105 | Hoge    |     315
     206 | Hoge    |     326
     207 | Hoge    |     327
(7 rows)

naruto=# SELECT setval('table6_column1_seq',
    (SELECT MAX(column1) FROM table6));
 setval
--------
    207
(1 row)

naruto=# INSERT INTO table6 (column1, column2, column3) VALUES (308, 'Fuga', 338);
INSERT 0 1
naruto=# SELECT setval('table6_column1_seq',
naruto(#     (SELECT MAX(column1) FROM table6));
 setval
--------
    308
(1 row)

naruto=# INSERT INTO table6 (column2, column3) VALUES ('Fuga', 339);
INSERT 0 1
naruto=# select * from table6;
 column1 | column2 | column3
---------+---------+---------
       1 |         |     301
       2 |         |     302
       3 |         |     303
       4 | Hoge    |     304
     105 | Hoge    |     315
     206 | Hoge    |     326
     207 | Hoge    |     327
     308 | Fuga    |     338
     309 | Fuga    |     339
(9 rows)

Identity に紐づく Sequence の名前

さて本題。
先に述べたとおり、 Identity列に紐づく Sequence 名前が {table名}_{column名}_seq になるとは限らない。例えば、その名前の Sequence が存在するときなど。

naruto=# CREATE SEQUENCE table7_column1_seq;
CREATE SEQUENCE
naruto=# CREATE TABLE table7(
naruto(#     column1 int GENERATED BY DEFAULT AS IDENTITY CONSTRAINT PK_table7 PRIMARY KEY
naruto(# );
CREATE TABLE
naruto=# SELECT C.oid, C.relname, C.relkind FROM PG_CLASS C
naruto-#     WHERE
naruto-#         C.relname LIKE 'table7%';
  oid  |       relname       | relkind
-------+---------------------+---------
 16651 | table7              | r
 16647 | table7_column1_seq  | S
 16649 | table7_column1_seq1 | S
(3 rows)

従って、次のような問題が発生する。

Identity列Identity列に紐づく Sequence 名前はどのように求めるのか。

調べた範囲ではそのあたりに言及している資料を見つけられなかった。
System Catalog を眺めてみると「pg_depend」というテーブルがあった。

このテーブルを pg_catalog と join すると Identity列に紐づく Sequence の名前を取得できる。

naruto=# SELECT
naruto-#         D.objid
naruto-#         , CD.relname
naruto-#         , CD.relkind
naruto-#         , D.refobjid
naruto-#         , CR.relname
naruto-#         , D.refobjsubid
naruto-#         , D.deptype
naruto-#     FROM
naruto-#         PG_DEPEND D
naruto-#         INNER JOIN PG_CLASS CD
naruto-#         ON CD.oid = D.objid
naruto-#         INNER JOIN PG_CLASS CR
naruto-#         ON CR.oid = D.refobjid
naruto-#     WHERE
naruto-#         CR.relname = 'table7';
 objid |       relname       | relkind | refobjid | relname | refobjsubid | dept
ype
-------+---------------------+---------+----------+---------+-------------+-----
----
 16649 | table7_column1_seq1 | S       |    16651 | table7  |           1 | i
(1 row)

これを利用して値の設定も可能である。4

naruto=# INSERT INTO table7 VALUES(101);
INSERT 0 1
naruto=# SELECT SETVAL(
naruto(#     (
naruto(#         SELECT
naruto(#             CD.relname::text
naruto(#         FROM
naruto(#             PG_DEPEND D
naruto(#             INNER JOIN PG_CLASS CD
naruto(#             ON CD.oid = D.objid
naruto(#             INNER JOIN PG_CLASS CR
naruto(#             ON CR.oid = D.refobjid
naruto(#         WHERE
naruto(#             CR.relname = 'table7'
naruto(#             AND CD.relkind='S')
naruto(#      , (SELECT max(column1) FROM table7));
 setval
--------
    101
(1 row)

naruto=# INSERT INTO table7 DEFAULT VALUES;
INSERT 0 1
naruto=# SELECT * FROM table7;
 column1
---------
     101
     102
(2 rows)

naruto=# INSERT INTO table7 VALUES(203);
INSERT 0 1
naruto=# SELECT SETVAL(
naruto(#     (
naruto(#         SELECT
naruto(#             CD.oid
naruto(#         FROM
naruto(#             PG_DEPEND D
naruto(#             INNER JOIN PG_CLASS CD
naruto(#             ON CD.oid = D.objid
naruto(#             INNER JOIN PG_CLASS CR
naruto(#             ON CR.oid = D.refobjid
naruto(#         WHERE
naruto(#             CR.relname = 'table7'
naruto(#             AND CD.relkind='S')
naruto(#      , (SELECT max(column1) FROM table7));
 setval
--------
    203
(1 row)

naruto=# INSERT INTO table7 DEFAULT VALUES;
INSERT 0 1
naruto=# SELECT * FROM table7;
 column1
---------
     101
     102
     203
     204
(4 rows)

終わりに

単発の作業の場合は Identity列 に紐づく Sequence名として {table名}_{column名}_seq を使ってもいいかもしれないが、汎用のツールや永続的に使う場合は Sequence名が通常とは違う場合も考慮した方がいいでしょう。5

  1. INSERT文ですべての列に既定の値・自動生成される値を設定する場合、DEFAULT VALUES句を用いる。 ↩

  2. INSERT文での RETURNING句は 挿入したレコードの指定の列を返す。複数行挿入した場合は複数列返される。今回の Identity列 のように挿入の際に自動生成した値を取り出すのに有効。 ↩

  3. INSERT文の DEFAULT句を指定することは可能 ↩

  4. SETVAL の第1引数は regclass型で、integer が渡されたときは PG_Class の oid として regclass型にキャストされるため、 Sequence の oid を渡すことが可能 ↩

  5. 一つのテーブルに複数の identity列を設定することは無いとは思うがその場合には pg_depend.refobjsubid に対する条件を付ければどの列に紐づく Sequence かを限定できるようになる。 ↩

1
0
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0