PostgreSQL 既存データに対して重複しなくなるまで加算してからインサートしたい

Question

実現したいこと

datetime型のカラムに対して、既存データに対して重複しなくなるまで秒を加算してからインサートしたい。
既存データには変更は加えない（DELETEやUPDATEはしない）。

例

'2024-08-29 09:00:00'をインサートしようとしたときに既に下記テーブルにデータがある。

|dt(timestamp without timezone)|
|------------------------------------|
|2024-08-29 09:00:00|
|2024-08-29 09:00:01|
|2024-08-29 09:00:02|

既存データと重複しなくなるまで秒を加算したいので、インサート後は下記のようにしたい。

|dt(timestamp without timezone)|
|------------------------------------|
|2024-08-29 09:00:00|
|2024-08-29 09:00:01|
|2024-08-29 09:00:02|
|2024-08-29 09:00:03|     <--- インサート

前提

テーブル構造
実際は他にもカラムがあるが、本質問では最低限の情報のみ提示したいためdatetime型(timestamp without timezone)列のみの構造とする。ただし、実際のテーブルでは他の列に主キーなどを設定しているため、主キー等は設定しない。（本当は主キーなどを設定して、重複しない構造にすべきなのでしょうが･･･。）

その他
Pythonのpsycopg2でSQLを使うつもり。
特に気にする必要はないと思いますが、一応。

バージョン

SELECT * FROM version();

PostgreSQL 14.4, compiled by Visual C++ build 1914, 64-bit

Answer 1

テーブル定義は以下のようなものであるという認識です。

CREATE TABLE TBL (val CHAR(1), dt timestamp without time zone);
INSERT INTO TBL VALUES ('A', TIMESTAMP '2024-08-29 09:00:00');
INSERT INTO TBL VALUES ('B', TIMESTAMP '2024-08-29 09:00:01');
INSERT INTO TBL VALUES ('C', TIMESTAMP '2024-08-29 09:00:02');

datetime型のカラムに対して、既存データに対して重複しなくなるまで秒を加算してからインサートしたい。

やり方はいろいろありそうですが、たとえば以下のようなINSERT文で実現可能だと思います。

WITH RECURSIVE R (dt) AS (
    SELECT TIMESTAMP '2024-08-29 09:00:00'
  UNION ALL
    SELECT dt + INTERVAL '1 SECONDS'
    FROM R
    WHERE EXISTS (
      SELECT *
      FROM TBL AS T
      WHERE R.dt = T.dt
    )
)
INSERT INTO TBL (val, dt) VALUES ('X', (SELECT MAX(dt) FROM R));

Comments

[person]

回答ありがとうございます。

RECURSIVEで再帰（反復？）処理したほうがSQLコスト低そうですかね。

[shiketa]

すでに解決済みですが。
DBMSによっては、RECURSIVEに上限があるらしいです。
丸一日分(24h*60m*60s)86400件連続していたらどうなるか興味があるのでためしてみました。
insert into tbl (dt) select generate_serise('2024-08-29 09:00:00'::timestamp, '2024-08-30 09:00:00'::timestamp, interval '1 second');
PostgreSQLではこの程度では上限には達しないようです。上限の設定がないのかもしれません。
ただし、結果が帰るまでには相当な時間がかかりました(docker環境)。
どんなシステムなのか想像ができませんが、一か月連続して埋まっていたりすると、結構厳しいのかもしれませんね。
ちなみに、dtにindexを設定すると処理時間は相当改善されました。
参考まで。

Answer 2

おもしろそうなのでためしてみた。

create table hoge (dt timestamp without time zone);

insert into hoge values
 ('2024-08-29 09:00:00'),
 ('2024-08-29 09:00:01'),
 ('2024-08-29 09:00:02'),
 ('2024-08-29 09:02:22')
 ;

with
  aaa as
  (
    SELECT 
      generate_series(min(dt), max(dt), interval '1 second') as dt
    from hoge
  ), 
  bbb as
  (
    select * from aaa
    except
    select * from hoge
  )
insert into hoge select * from bbb
;

select * from hoge order by dt;

2024-08-29 09:00:00.000
2024-08-29 09:00:01.000
2024-08-29 09:00:02.000
2024-08-29 09:00:03.000
2024-08-29 09:00:04.000
...
2024-08-29 09:02:20.000
2024-08-29 09:02:21.000
2024-08-29 09:02:22.000

なるほど。

Comments

[person]

回答ありがとうございます。

ご提示のSQLは、
aaa: テーブル内の最小値(2024-08-29 09:00:00)～最大値(2024-08-29 09:02:22)の1秒刻みデータ
bbb: aaaにありhogeにないデータ(2024-08-29 09:00:03～2024-08-29 09:02:21)
を取得してbbbをインサートしていると思います。

質問文に記載したように1つのデータだけインサートする場合は、dtをORDER BY で昇順ソートしてLIMIT1で1行のみSELECTしてINSERTするで合っていますか？
> insert into hoge select * from bbb
insert into hoge select * from bbb ORDER BY dt LIMIT 1;

[shiketa]

最大値の次、であればこんな感じでもいいのでは?
insert into hoge
select max(dt) + interval '1 second' from hoge;

[person]

すみません、書き方が悪かったですね。
インサートしたい値は、
hogeにある最大値+1ではなく、
入れようとした値(2024-08-29 09:00:00)に重複しなくなるまで秒を足した値のデータを1件だけです。

2024-08-29 09:00:00.000
2024-08-29 09:00:01.000
2024-08-29 09:00:02.000
2024-08-29 09:00:22.000

↓

2024-08-29 09:00:00.000
2024-08-29 09:00:01.000
2024-08-29 09:00:02.000
2024-08-29 09:00:03.000 <--- insert
2024-08-29 09:00:22.000