PostgreSQLでserialをintegerに変更する

integerをserialに変更する方法は多く見つかったが、逆についての記事があまり見つからなかったので記事にしておく。

serial型とは

自動で採番を行ってくれるデータ型のこと。カラムにserial型を指定すると、対応したsequenceが作られる。

postgres=# CREATE TABLE table_name (
    column_name SERIAL
);
CREATE TABLE

postgres=# \d
                     List of relations
 Schema |            Name            |   Type   |  Owner   
--------+----------------------------+----------+----------
 public | table_name                 | table    | postgres
 public | table_name_column_name_seq | sequence | postgres
(2 rows)

ダメな例

単純に考えると、ALTER COLUMN で serial を integer に変更すると良さそうではあるが、この方法ではうまくいかない。実行の前後でテーブル定義に変更がないことが確認できる。

postgres=# \d table_name
                                   Table "public.table_name"
   Column    |  Type   | Collation | Nullable |                     Default                     
-------------+---------+-----------+----------+-------------------------------------------------
 column_name | integer |           | not null | nextval('table_name_column_name_seq'::regclass)

postgres=# ALTER TABLE table_name ALTER COLUMN column_name TYPE integer
ALTER TABLE

postgres=# \d table_name
                                   Table "public.table_name"
   Column    |  Type   | Collation | Nullable |                     Default                     
-------------+---------+-----------+----------+-------------------------------------------------
 column_name | integer |           | not null | nextval('table_name_column_name_seq'::regclass)

PosrgreSQL文書にも記述があるが、serial は正確にはデータ型ではなく、数値型に「デフォルト値をシーケンスから取得し、シーケンスの値を1つ進める」という制約をつけたものとして実装されている。 上記のようにSERIAL型を指定してテーブルを定義すると、以下のようにシーケンスも合わせて定義したことになる。

CREATE SEQUENCE table_name_column_name_seq AS integer;
CREATE TABLE table_name (
    column_name integer NOT NULL DEFAULT nextval('table_name_column_name_seq')
);
ALTER SEQUENCE table_name_column_name_seq OWNED BY table_name.column_name;

良い例

上記のような制約を解除する必要があるため、DROP DEFAULT を利用して、数値型に設定されているデフォルト値を削除する。

postgres=# ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT
ALTER TABLE

postgres=# \d table_name
               Table "public.table_name"
   Column    |  Type   | Collation | Nullable | Default 
-------------+---------+-----------+----------+---------
 column_name | integer |           | not null |

ただし、上記の操作を行なってもシーケンスは残ったままになる。残っていても動作には問題ないと思われるが、念のため削除しておく。

postgres=# \d
                     List of relations
 Schema |            Name            |   Type   |  Owner   
--------+----------------------------+----------+----------
 public | table_name                 | table    | postgres
 public | table_name_column_name_seq | sequence | postgres
(2 rows)

postgres=# DROP SEQUENCE table_name_column_name_seq;
DROP SEQUENCE

postgres=# \d
           List of relations
 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 public | table_name | table | postgres
(1 row)

これで完了。

参考URL