From: Tom Lane <tgl@sss.pgh.pa.us>
To: Igal Sapir <igal@lucee.org>
cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: Passing a dynamic interval to generate_series()
In-reply-to: <CA+zig0-suhFUeS7xcJyZ=4HDFXJ3Kn2adXo3B4zHUzDFgMscCw@mail.gmail.com>
References: <CA+zig0-suhFUeS7xcJyZ=4HDFXJ3Kn2adXo3B4zHUzDFgMscCw@mail.gmail.com>
Comments: In-reply-to Igal Sapir <igal@lucee.org>
	message dated "Sun, 30 Jun 2024 15:39:26 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2537941.1719787874.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 30 Jun 2024 18:51:14 -0400
Message-ID: <2537942.1719787874@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2537942.1719787874%40sss.pgh.pa.us>
Precedence: bulk

Igal Sapir <igal@lucee.org> writes:
> But this throws an error (SQL Error [42601]: ERROR: syntax error at or n=
ear
> "'1 '"):

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

You're overthinking it.

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

------------------------
 2024-06-01 00:00:00-04
 2024-07-01 00:00:00-04
 2024-08-01 00:00:00-04
 2024-09-01 00:00:00-04
 2024-10-01 00:00:00-04
 2024-11-01 00:00:00-04
 2024-12-01 00:00:00-05
 2025-01-01 00:00:00-05
(8 rows)

It might help to read this:

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX=
-CONSTANTS-GENERIC

and to experiment with what you get from the constituent elements
of what you tried, rather than trying to guess what they are from
generate_series's behavior.  For example,

select (interval '1 ');
 interval =

----------
 00:00:01
(1 row)

select (interval '1 ' || 'month');
   ?column?    =

---------------
 00:00:01month
(1 row)

			regards, tom lane