PostgreSQLカラムの属性変更

途中でカラムの属性を変更したいときありますよね。
今回は、PostgreSQLでの変更方法を記載します。

前置き

• AUTO_INCREMENT属性はMySQL用なので、PostgreSQLでは使用できない。

• CREATE TABLE実行時に "serial" を型に指定すると、対応するシーケンスを自動生成してnextval()してくれるようになる。

• integer型で定義したものを、後からALTER文でserial型に変えることはできない。

• 既存のカラムをserialに変更したい場合、手動でシーケンスを作って対処する。

手順

シーケンス作成

# create sequence test2_id1_seq;
CREATE SEQUENCE
# \d
              List of relations
 Schema |     Name      |   Type   |  Owner
--------+---------------+----------+----------
 public | test2         | table    | postgres
 public | test2_id1_seq | sequence | postgres
(8 rows)

id1カラムをserial型に変更

# alter table test2 alter id1 set default nextval('test2_id1_seq');

# \d test2
                          Table "public.test2"
 Column |  Type   |                      Modifiers
--------+---------+-----------------------------------------------------
 id1    | integer | not null default nextval('test2_id1_seq'::regclass)
 id2    | integer | not null
 id3    | integer | not null
Indexes:
    "test2_pkey" PRIMARY KEY, btree (id1, id2, id3)

シーケンス（test2_id1_seq）を指定したテーブル（test2.id1）に関連づける。

# alter sequence test2_id1_seq owned by test2.id1;
ALTER SEQUENCE

# \d test2
                          Table "public.test2"
 Column |  Type   |                      Modifiers
--------+---------+-----------------------------------------------------
 id1    | integer | not null default nextval('test2_id1_seq'::regclass)
 id2    | integer | not null
 id3    | integer | not null
Indexes:
    "test2_pkey" PRIMARY KEY, btree (id1, id2, id3)

シーケンスを100からはじめたい場合。

# select setval('test2_id1_seq',100);

 setval
--------
    100
(1 row)

INSERT前.

# select * from test2;
 id1 | id2 | id3
-----+-----+-----
   1 |   2 |   3
   2 |   3 |   1
(2 rows)

INSERT後.

# insert into test2 (id2,id3) values('4','4');
INSERT 0 1

# select * from test2;
 id1 | id2 | id3
-----+-----+-----
   1 |   2 |   3
   2 |   3 |   1
 101 |   4 |   4
(3 rows)

その他検証

• 複数カラムにプライマリキー設定できるか？

→上記と同じ手順で設定変更。

# \d test2
                          Table "public.test2"
 Column |  Type   |                      Modifiers
--------+---------+-----------------------------------------------------
 id1    | integer | not null default nextval('test2_id1_seq'::regclass)
 id2    | integer | not null default nextval('test2_id2_seq'::regclass)
 id3    | integer | not null
Indexes:
    "test2_pkey" PRIMARY KEY, btree (id1, id2, id3)

→OK

• ユニークでないカラム（id3）を途中からserial型に変更できるか？

# select * from test2;
 id1 | id2  | id3
-----+------+-----
   1 |    2 |   3
   2 |    3 |   1
 101 |    4 |   4
 102 | 1001 |  33    ★
 103 | 1002 |  44
 105 | 1003 |  33    ★
(6 rows)

シーケンスを10000からはじめるように設定

# select setval('test2_id3_seq',10000);
 setval
--------
  10000
(1 row)

# insert into test2 (id4) values('11');
INSERT 0 1
# insert into test2 (id4) values('11');
INSERT 0 1
# select * from test2;
 id1 | id2  |  id3  | id4
-----+------+-------+-----
   1 |    2 |     3 |
   2 |    3 |     1 |
 101 |    4 |     4 |
 102 | 1001 |    33 |         ★
 103 | 1002 |    44 |
 105 | 1003 |    33 |         ★
 106 | 1004 | 10001 |  11
 107 | 1005 | 10002 |  11
 108 | 1006 | 10003 |  11
(9 rows)

→重複しているカラムでも途中からserial型にできた！