MIME-Version: 1.0
From: Igal Sapir <igal@lucee.org>
Date: Sun, 30 Jun 2024 15:39:26 -0700
Message-ID: <CA+zig0-suhFUeS7xcJyZ=4HDFXJ3Kn2adXo3B4zHUzDFgMscCw@mail.gmail.com>
Subject: Passing a dynamic interval to generate_series()
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000cc7991061c23275d"
Archived-At: <https://www.postgresql.org/message-id/CA%2Bzig0-suhFUeS7xcJyZ%3D4HDFXJ3Kn2adXo3B4zHUzDFgMscCw%40mail.gmail.com>
Precedence: bulk

--000000000000cc7991061c23275d
Content-Type: text/plain; charset="UTF-8"

Hello,

I am trying to pass a dynamic interval to generate_series() with date range.

This works as expected, and generates a series with an interval of 1 month:

SELECT generate_series(
    date_trunc('month', current_date),
    date_trunc('month', current_date + interval '7 month'),
    interval '1 month'
)


This works as expected and returns an interval of 1 month:

SELECT ('1 ' || 'month')::interval;


But this throws an error (SQL Error [42601]: ERROR: syntax error at or near
"'1 '"):

SELECT generate_series(
    date_trunc('month', current_date),
    date_trunc('month', current_date + interval '7 month'),
    interval ('1 ' || 'month')::interval
)


And this returns a series with interval of 1 second??

SELECT generate_series(
    date_trunc('month', current_date),
    date_trunc('month', current_date + interval '7 month'),
    (interval '1 ' || 'month')::interval
)

Because this returns an interval of 1 second:

SELECT (interval '1 ' || 'month')::interval;

Is that a bug?


I am able to work around the issue using a CASE statement, but shouldn't it
work simply by concatenating the string with the || operator?

Thank you,

Igal

--000000000000cc7991061c23275d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hello,</div><div><br></div><div>I am trying to pass a=
 dynamic interval to generate_series() with date range.</div><div><br></div=
><div>This works as expected, and generates a series with an interval of 1 =
month:</div><div><br></div>SELECT 	generate_series(<br>=C2=A0 =C2=A0 date_t=
runc(&#39;month&#39;, current_date),<br>=C2=A0 =C2=A0 date_trunc(&#39;month=
&#39;, current_date + interval &#39;7 month&#39;),<br>=C2=A0 =C2=A0 interva=
l &#39;1 month&#39;<br><div>)</div><div><br></div><div><br></div><div>This =
works as expected and returns an interval of 1 month:</div><div><br></div><=
div>SELECT 	(&#39;1 &#39; || &#39;month&#39;)::interval;</div><div><br></di=
v><div><br></div><div>But this throws an error (SQL Error [42601]: ERROR: s=
yntax error at or near &quot;&#39;1 &#39;&quot;):</div><div><br></div><div>=
SELECT 	generate_series(<br>=C2=A0 =C2=A0 date_trunc(&#39;month&#39;, curre=
nt_date),<br>=C2=A0 =C2=A0 date_trunc(&#39;month&#39;, current_date + inter=
val &#39;7 month&#39;),<br>=C2=A0 =C2=A0 interval (&#39;1 &#39; || &#39;mon=
th&#39;)::interval<br>)</div><div><br></div><div><br></div><div>And this re=
turns a series with interval of 1 second??</div><div><br></div><div>SELECT =
	generate_series(<br>=C2=A0 =C2=A0 date_trunc(&#39;month&#39;, current_date=
),<br>=C2=A0 =C2=A0 date_trunc(&#39;month&#39;, current_date + interval =
9;7 month&#39;),<br>=C2=A0 =C2=A0 (interval &#39;1 &#39; || &#39;month&#39;=
)::interval<br>)</div><div><br></div><div>Because this returns an interval =
of 1 second:</div><div><br></div><div>SELECT 	(interval &#39;1 &#39; || =
9;month&#39;)::interval;</div><div><br></div><div>Is that a bug?</div><div>=
<br></div><div><br></div><div>I am able to work around the issue using a CA=
SE statement, but shouldn&#39;t it work simply by concatenating the string =
with the || operator?</div><div><br></div><div>Thank you,</div><div><br></d=
iv><div>Igal<br></div><div><br></div><div><br></div></div>

--000000000000cc7991061c23275d--