Postgresqlでintervalの値を動的に指定する（＋`generate_series`を使った方法）メモ

今振り返ってみるとしょうもないことなんだが、Postgresqlのintervalの使い方でちょっと詰まったので、そのメモ。副問い合わせでgenerate_series使って連番を生成し、その連番に基づいてcurrent_timestampからの差分をぶわーっと一気に作ろうという魂胆があって、無駄に色々やってみたんだが、そもそも最初の発想に誤りがありましたという話。

※失敗例

最初はこんな感じのSQLを書いて実行してみた。

postgres=# select g.g, current_timestamp - interval g.g || ' days' from (select generate_series(1,10) g) g;

副問い合わせ (select generate_series(1,10) g) g で1から10までの連番を生成して、その生成した数値をintervalに動的に指定する形で CURRENT_TIMESTAMP から日付の減算を実施している(current_timestamp - interval g.g || ' days')。うまくいけば「過去10日分のTIMESTAMPを生成する」SQLになる、はずだった。
が、これは以下のエラーになる。

ERROR:  syntax error at or near "."
LINE 1: select g.g, current_timestamp - interval g.g || ' days' from...

current_timestamp - interval '1 days'みたいに、intervalの指定が固定の文字列なら成功するのだが、肝心の数値の部分を動的にすると絶対に同じエラーで落ちる。

• 数値が変に入ってくる（intervalの指定値に暗黙の型変換を挟んでいる）のがだめなのかな？と思って interval g.g::char || ' days'とかして、つまりgenerate_seriesの値をcharにcastして'days'と文字列結合して試してみたり
• だったら最初から副問い合わせ内で指定する文字列自体を生成しておけばいいのか？とか思って副問い合わせの中身をselect generate_series(1,10) || ' days' gにして、interval g.gだけにしてみたり

と、今にして思うと 涙ぐましい悪あがき を色々してみたんだが、どれも同じエラーで落ちる。

調べたんだが、そもそもintervalにはこういう動的な値の指定ができないようだ（そういう仕様ということのようだ）。こちらのissueの回答でそのような内容が確認できる。

修正案

intervalに指定する文字をつくって::intervalでcastすればいい。そんだけ。書き方が少し違うだけである。最初の「※失敗例」をもとに修正すると以下のようになる。

postgres=# select g.g, current_timestamp - (g.g || ' days')::interval from (select generate_series(1,10) g) g;
 g  |           ?column?            
----+-------------------------------
  1 | 2023-11-13 13:33:54.551507+00
  2 | 2023-11-12 13:33:54.551507+00
  3 | 2023-11-11 13:33:54.551507+00
  4 | 2023-11-10 13:33:54.551507+00
  5 | 2023-11-09 13:33:54.551507+00
  6 | 2023-11-08 13:33:54.551507+00
  7 | 2023-11-07 13:33:54.551507+00
  8 | 2023-11-06 13:33:54.551507+00
  9 | 2023-11-05 13:33:54.551507+00
 10 | 2023-11-04 13:33:54.551507+00
(10 rows)

要するにcurrent_timestamp - interval g.g || ' days'がcurrent_timestamp - (g.g || ' days')::intervalに変わっただけである。これでやりたいことができた。これについてはこちらの記事から情報を得た。どうもありがとうございました。（？）

（余談）generate_seriesの引数にTIMESTAMPを与えて連日を生成する

generate_seriesの引数にはTIMESTAMP型も指定できるので、以下のようなやり方で似たような結果は得られる。

postgres=# select generate_series(current_timestamp , current_timestamp - interval '10 days', ('-1 days')::interval);
        generate_series        
-------------------------------
 2023-11-14 13:57:32.688607+00
 2023-11-13 13:57:32.688607+00
 2023-11-12 13:57:32.688607+00
 2023-11-11 13:57:32.688607+00
 2023-11-10 13:57:32.688607+00
 2023-11-09 13:57:32.688607+00
 2023-11-08 13:57:32.688607+00
 2023-11-07 13:57:32.688607+00
 2023-11-06 13:57:32.688607+00
 2023-11-05 13:57:32.688607+00
 2023-11-04 13:57:32.688607+00
(11 rows)

この例だとcurrent_timestamp自体も返却されるので、↑と比べると若干結果が異なるが、まあそこはいくらでも調整できる。intervalが多くなるのでちょっとgenerate_seriesの指定がゴチャゴチャするが、変に副問い合わせを使わなくてよい分こっちの方が分かりやすいかもしれない。。

以上