[PostgreSQL]serial と 手動でのシーケンス作成の違い

データベース

serial を使ったカラムの作成と手動でシーケンスを作成した場合で違いがある。

要は以下の様に作成した table1 と table2 は同じではないというのを知らなかった。

1CREATE TABLE table1 (
2    id SERIAL ,
3    name text
4);
1CREATE SEQUENCE table2_id_seq;
2CREATE TABLE table2 (
3    id integer DEFAULT nextval('table2_id_seq') NOT NULL,
4    name text
5);

1. 検証した PostgreSQL のバージョン

1SELECT version();
2                                                 version
3----------------------------------------------------------------------------------------------------------
4 PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
5(1 行)

2. 検証

まずは事前にレコードを追加しておき

 1INSERT INTO table1 (name) VALUES ('test');
 2INSERT INTO table1 (name) VALUES ('test2');
 3INSERT INTO table2 (name) VALUES ('test');
 4INSERT INTO table2 (name) VALUES ('test2');
 5
 6SELECT * FROM table1;
 7 id | name
 8----+-------
 9  1 | test
10  2 | test2
11(2 行)
12
13SELECT * FROM table2;
14 id | name
15----+-------
16  1 | test
17  2 | test2
18(2 行)

2.1. truncate

 1TRUNCATE TABLE table1 RESTART IDENTITY;
 2TRUNCATE TABLE table2 RESTART IDENTITY;
 3
 4SELECT * FROM table1_id_seq;
 5 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
 6------------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 7 table1_id_seq |          1 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | f
 8(1 行)
 9
10SELECT * FROM table2_id_seq;
11 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
12-------------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
13 table2_id_seq |          2 |           1 |            1 | 9223372036854775807 |         1 |           1 |      31 | f         | t
14(1 行)

table1 はシーケンス番号がリセットされるが、 table2 はシーケンス番号がリセットされない。

 1INSERT INTO table1 (name) VALUES ('test');
 2INSERT INTO table1 (name) VALUES ('test2');
 3INSERT INTO table2 (name) VALUES ('test');
 4INSERT INTO table2 (name) VALUES ('test2');
 5
 6SELECT * FROM tablename;
 7 id | name
 8----+-------
 9  1 | test
10  2 | test2
11(2 行)
12
13SELECT * FROM tablename2;
14 id | name
15----+-------
16  3 | test
17  4 | test2
18(2 行)

レコード追加時も table2 は続きのシーケンス番号で id 値が振られている。

2.2. drop table

列を削除したときの挙動も異なる。

列では無くテーブル削除ですが、

1DROP TABLE table1;
2DROP TABLE table2;
1SELECT * FROM table1_id_seq;
2ERROR:  relation "table1_id_seq" does not exist
3行 1: SELECT * FROM table1_id_seq;
4                    ^

table1 のシーケンスは自動的に削除される。

1SELECT * FROM table2_id_seq;
2 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
3-------------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
4 table2_id_seq |          4 |           1 |            1 | 9223372036854775807 |         1 |           1 |      31 | f         | t
5(1 行)

table2 のシーケンスは自動的に削除されない。

3. 同じ挙動とするためには

PostgreSQL 8.2 以降

table2 も table1 と同じ挙動とするためには ALTER SEQUENCE <シーケンス名> OWNED BY <テーブル名> + "." + <カラム名> が必要となる。

1ALTER SEQUENCE table2_id_seq OWNED BY table2.id;

3.1. truncate

レコード追加して

1INSERT INTO table2 (name) VALUES ('test');
2INSERT INTO table2 (name) VALUES ('test2');

truncate する。

 1TRUNCATE TABLE table2 RESTART IDENTITY;
 2
 3SELECT * FROM table2_id_seq;
 4 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
 5 table2_id_seq |          1 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | f
 6
 7INSERT INTO table2 (name) VALUES ('test');
 8INSERT INTO table2 (name) VALUES ('test2');
 9
10SELECT * FROM table2;
11  1 | test
12  2 | test2

id がリセットされて 1 から振られている。

3.2. drop table

列の削除により自動的にシーケンスの削除もされる。

1DROP TABLE table2;
2SELECT * FROM table2_id_seq;
3ERROR:  relation "table2_id_seq" does not exist
4行 1: SELECT * FROM table2_id_seq;

シーケンスも削除された。

4. pg_dump の違い

  • table1 : serial を使用

  • table2 : シーケンス手動作成（ALTER SEQUENCE …​ OWNED BY）

  • table3 : シーケンス手動作成

それぞれ3つの場合に違いがあるのか見てみる。

table1 作成
1CREATE TABLE table1 (
2    id SERIAL ,
3    name text
4);
table2 作成
1CREATE SEQUENCE table2_id_seq;
2CREATE TABLE table2 (
3    id integer DEFAULT nextval('table2_id_seq') NOT NULL,
4    name text
5);
6ALTER SEQUENCE table2_id_seq OWNED BY table2.id;
table3 作成
1CREATE SEQUENCE table3_id_seq;
2CREATE TABLE table3 (
3    id integer DEFAULT nextval('table3_id_seq') NOT NULL,
4    name text
5);

4.1. pg_dumpの結果

table1 のダンプ箇所
 1CREATE TABLE table1 (
 2    id integer NOT NULL,
 3    name text
 4);
 5
 6CREATE SEQUENCE table1_id_seq
 7    START WITH 1
 8    INCREMENT BY 1
 9    NO MINVALUE
10    NO MAXVALUE
11    CACHE 1;
12
13ALTER SEQUENCE table1_id_seq OWNED BY table1.id;
14
15ALTER TABLE ONLY table1 ALTER COLUMN id SET DEFAULT nextval('table1_id_seq'::regclass);
table2 のダンプ箇所
 1CREATE TABLE table2 (
 2    id integer NOT NULL,
 3    name text
 4);
 5
 6CREATE SEQUENCE table2_id_seq
 7    START WITH 1
 8    INCREMENT BY 1
 9    NO MINVALUE
10    NO MAXVALUE
11    CACHE 1;
12
13ALTER SEQUENCE table2_id_seq OWNED BY table2.id;
14
15ALTER TABLE ONLY table2 ALTER COLUMN id SET DEFAULT nextval('table2_id_seq'::regclass);

  • table1 : serial を使用

  • table2 : シーケンス手動作成（ALTER SEQUENCE …​ OWNED BY）

この2つの場合は同じSQLがダンプされた。

table3 のダンプ箇所
 1CREATE SEQUENCE table3_id_seq
 2    START WITH 1
 3    INCREMENT BY 1
 4    NO MINVALUE
 5    NO MAXVALUE
 6    CACHE 1;
 7
 8CREATE TABLE table3 (
 9    id integer DEFAULT nextval('table3_id_seq'::regclass) NOT NULL,
10    name text
11);