【PostgreSQL】シーケンスを特定のグループ別に発行する

前置き

表題の通り。 テーブルAにrecord_idとline_noという項目が存在するとした場合に、record_id単位で新規にシーケンスを振りたい状況が出てきた。
※ record_idとline_noは1:Nの関係

こういう風に書けたら良かったのだけど、ウィンドウ関数ではないので書けないと怒られた。

nextval('MY_SEQ') over(partition by record_id)

解決策

↓これで意図した通りに動く。

case when lag(record_id) over(partition by record_id) is null then nextval('MY_SEQ')
     else currval('MY_SEQ')
 end

これらで使用しているのは簡単に言えば以下のような感じ*1。

• lag()は指定したカラムの値が前の行と変わったらnullを返す処理

• currval()は同一のセッション内で発行した直前のnextval()の値を返す処理

確認SQL

with data(record_id, line_no) as (
  values(10, 1)
       ,(10, 2)
       ,(10, 3)
       ,(11, 1)
       ,(11, 2)
       ,(12, 1)
)

select *
      ,case when lag(record_id) over(partition by record_id) is null then nextval('MY_SEQ')
            else currval('MY_SEQ')
        end as seq
  from data

結果

record_id	line_no	seq
10	1	1
10	2	1
10	3	1
11	1	2
11	2	2
12	1	3

雑感

「セッションAで↑のSQLを時間のかかるSQL追加して流し、セッションBでnextvalを流す」というようなテストをやってみたが、ちゃんとセッションAとBで取得されたシーケンスはそれぞれ独立していたので、重複する心配は無さそう。公式に書いてあってもどういう挙動をするか確認したくなるよね。

…ここまで書いておいてなんだけど、1:Nの関係にあるのは今回自分が必要になったケースで言えばJoinをかけているからで、副問い合わせで1の方のテーブルにシーケンスを発行すれば良かったのではと二日経って気付いた🤔

参考：

www.postgresql.jp

www.postgresql.jp

*1:詳細は下記の公式リンク参照