PostgreSQL

PostgreSQL 自動採番カラム

自動採番カラム

自動採番にしたいカラムを連番型で定義するだけで実現できます。Oracleに比べるととても楽ちんです。内部的にはシーケンスが作成され対象カラムにマッピングされます。以下連番型(serial)を指定すると実際は()の型で作成されます。

  • smallserial(smallint)  1~32767
  • serial4/serial(integer)  1~2147483647
  • serial8/bigserial(bigint)  1~9223372036854775807
create table  logging (
        logid           serial4     -- 自動採番
    ,   trace_no        integer
    ,   step_code       varchar(8)
    ,   step_message    varchar(128)
    ,   creation_date   timestamp
);
上記DDLで作成したテーブル定義を確認する
上記logidの内部的な定義は以下のようになります。

logid integer NOT NULL DEFAULT  nextval('logging_logid_seq'::regclass)

--テーブル構成を確認する
myposdb=# \d logging
                                            Table "sooni.logging"
    Column     |            Type             | Collation | Nullable |                Default
---------------+-----------------------------+-----------+----------+----------------------------------------
 logid         | integer                     |           | not null | nextval('logging_logid_seq'::regclass)
 trace_no      | integer                     |           |          |
 step_code     | character varying(8)        |           |          |
 step_message  | character varying(128)      |           |          |
 creation_date | timestamp without time zone |           |          |


myposdb=#


-- 自動で作成されたシーケンス(logging_logid_seq)を確認する
myposdb=# \x
Expanded display is on.
myposdb=# select * from information_schema.sequences where sequence_name ='logging_logid_seq';
-[ RECORD 1 ]-----------+------------------
sequence_catalog        | myposdb
sequence_schema         | sooni
sequence_name           | logging_logid_seq
data_type               | integer
numeric_precision       | 32
numeric_precision_radix | 2
numeric_scale           | 0
start_value             | 1
minimum_value           | 1
maximum_value           | 2147483647
increment               | 1
cycle_option            | NO


-- 参考までにですが自動で作成されたシーケンスの削除を試みると
-- 以下のようなエラーがでます。依存関係ができているので
-- 削除するならcascadeオプションが必要となります。
myposdb=# drop sequence logging_logid_seq;
ERROR:  cannot drop sequence logging_logid_seq because other objects depend on it
DETAIL:  default value for column logid of table logging depends on sequence logging_logid_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
myposdb=#
作成したテーブルへINSERTを行い動作を確認する
-- 自動採番対象のlogidを省略し2件のinsertを実施
myposdb=# insert into logging(trace_no,step_code,step_message,creation_date) values (10,'STEP01','処理開始',now());
INSERT 0 1
myposdb=# insert into logging(trace_no,step_code,step_message,creation_date) values (12,'STEP02','処理終了',now());
INSERT 0 1

myposdb=# select * from logging order by 1;
-[ RECORD 1 ]-+---------------------------
logid         | 1    <-- 自動で採番
trace_no      | 10
step_code     | STEP01
step_message  | 処理開始
creation_date | 2022-10-30 14:43:08.15044
-[ RECORD 2 ]-+---------------------------
logid         | 2    <-- 自動で採番
trace_no      | 12
step_code     | STEP02
step_message  | 処理終了
creation_date | 2022-10-30 14:44:15.04927

myposdb=#
自動採番対象カラムへも値を指定する事は可能です

上記の例では、自動採番対象カラムに対しINSERT時にカラムの指定はしていませんでしたが、指定する事も可能ですがこの場合少し注意が必要です。今回の例のように自動採番対象カラムをを設ける場合、PKに指定するようなシーンが多い事が予想されます。つまり、自動採番カラムに紐付くシーケンスの値と重複エラーにならないように実装の際には何か工夫が必要になります。この点、Oracleでは外部から値を指定できないように制限をつける事ができる(GENERATED ALWAYS AS IDENTITY属性)のでこの問題はクリアできるのですが、PostgreSQLではこのような機能は持っていないようです。(PostgreSQL Ver15時点)

-- ※logidカラムにユニークキー制約を付与している場合等は
-- 重複エラーになる事も想定が必要になります。
-- Oracleのように外部から値を指定できないように制限を付ける事は
-- どうやらできないようです。(今のところ私にはその方法が見つかっていません)
--
myposdb=# insert into logging(logid,trace_no,step_code,step_message,creation_date) values (5,12,'STEP10','2回目',now());
INSERT 0 1
myposdb=# select * from logging where logid=5;
-[ RECORD 1 ]-+---------------------------
logid         | 5
trace_no      | 12
step_code     | STEP10
step_message  | 2回目
creation_date | 2022-10-30 14:45:50.295825


myposdb=#

上記課題なんとか以下対策考えてみました。参考にどうぞ( 2023/07/10)

自動採番カラムに対し外部から指定できない仕組みをなんとか実装してみました

スポンサーリンク