PostgreSQL シーケンス操作の小技 - なかなかどうして、この有様よ。

概要

PostgreSQLのシーケンスでよく使う採番方法と、シーケンス操作や同一のSQLで複数回シーケンスを利用する場合の方法などをメモしています。
シーケンスについては以下ページを参考にしました。

シーケンス操作

前提として、PostgreSQLのシーケンスは非トランザクション処理のため、トランザクションがロールバックされても採番した値はロールバックされません。

シーケンスで次の値を取得する

シーケンスで次の値を取得したい場合、nextval()関数にシーケンス名を指定してselectを行います。

-- シーケンスの次の値の取得
SELECT nextval('seq_id');

シーケンスの現在値を取得する

シーケンスで現在の値を取得したい場合、currval()関数にシーケンス名を指定してselectを行います。

-- シーケンスの現在値の取得
SELECT currval('seq_id');

2020/01/15 追記

currval()関数については以下のPostgreSQLドキュメントにあるように、セッション内でのnextval()関数で取得した現在値を表示するようです。

currval
現在のセッションにおいて、そのシーケンスから nextval によって取得された直近の値を返します。 (セッション内で、シーケンスに対し nextval が呼ばれていない場合には、エラーが報告されます。)

なので、nextval()関数が同一セッション内で実行されていない場合は表示できません。
（私も知らなかったので、currval()関数で今のシーケンスの値は何だろうと確認しようとしてエラーになって初めて知りました。）

シーケンスの初期化

シーケンスを初期化したい場合は、setval()関数にシーケンス名、シーケンス作成時に指定した範囲内の値を指定してselectを行います。

-- シーケンスの初期化
SELECT setval('seq_id', 1);

また、既にあるテーブルのIDの件数を設定したい場合は、setval()関数内にSQL文を記載して取得した結果を設定することが出来ます。

-- シーケンスの初期化(トランテーブルの件数を設定)
SELECT
    setval( 
    'seq_id'
    , ( 
      SELECT
          COUNT(T.ID) 
      FROM
        TRN_DATA T
    )
  );

2020/01/14 追記

count()関数ではなくmax()関数を使ってみたところエラーとなったので、count()関数のみであれば可能のようです。

同一のSQLで複数のシーケンスを利用する場合

また、同一のSQL文の中でシーケンスを取得してその値を別のカラムにも設定したい、という場合、以下のようなSQL文を記載することで同一の値を利用する事が出来ます。

-- シーケンスので採番＋採番した値を別カラムにも利用
SELECT
    nextval('seq_id') as id
  , currval('seq_id') as create_no;

ちなみに、この時どちらもnextval()関数を使用すると、それぞれで採番されるので、同一の値になりません。
そのため、最初にnextval()関数で採番し、それ以降はcurrval()関数で採番後の現在値を利用するようにします。

そして、以下のようにシーケンスで採番した値を0埋めして取得するなども可能になります。

-- シーケンスので採番＋採番した値のLpadの0埋め
-- ※ to_char時に先頭にスペースが入るので、一旦trimをかけてからlpadして0埋めする
SELECT
    nextval('seq_id') as id
  , lpad( 
    trim( 
      to_char(currval('seq_id'), '99999')
    ) 
    , 5
    , '0'
  ) as create_no;