Thread: help with generation_series in pg10
Hi,
In pg10 generation series doesn't work like in 9.5.
Ex. in 9.5:
z=# select generate_series(1, 10), generate_series(1, 5);
generate_series | generate_series
-----------------+-----------------
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 | 1
7 | 2
8 | 3
9 | 4
10 | 5
(10 registros)
so, in version 10 the same sql show different result set.
z=# select generate_series(1, 10), generate_series(1,5);
generate_series | generate_series
-----------------+-----------------
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 |
7 |
8 |
9 |
10 |
(10 registros)
how can i have the same in pg10 as i have had in pg 9.x?
I need it to date type to... if possible.
--
Att.
Márcio A. Sepp
On 01/08/2018 05:25 PM, Márcio A. Sepp wrote: > > Hi, > > > In pg10 generation series doesn't work like in 9.5. > Ex. in 9.5: > z=# select generate_series(1, 10), generate_series(1, 5); > generate_series | generate_series > -----------------+----------------- > 1 | 1 > 2 | 2 > 3 | 3 > 4 | 4 > 5 | 5 > 6 | 1 > 7 | 2 > 8 | 3 > 9 | 4 > 10 | 5 > (10 registros) > > > so, in version 10 the same sql show different result set. The reason why: https://www.postgresql.org/docs/10/static/release-10.html#idm46428657945600 Change the implementation of set-returning functions appearing in a query's SELECT list (Andres Freund) Set-returning functions are now evaluated before evaluation of scalar expressions in the SELECT list, much as though they had been placed in a LATERAL FROM-clause item. This allows saner semantics for cases where multiple set-returning functions are present. If they return different numbers of rows, the shorter results are extended to match the longest result by adding nulls. Previously the results were cycled until they all terminated at the same time, producing a number of rows equal to the least common multiple of the functions' periods. In addition, set-returning functions are now disallowed within CASE and COALESCE constructs. For more information see Section 37.4.8. > z=# select generate_series(1, 10), generate_series(1,5); > generate_series | generate_series > -----------------+----------------- > 1 | 1 > 2 | 2 > 3 | 3 > 4 | 4 > 5 | 5 > 6 | > 7 | > 8 | > 9 | > 10 | > (10 registros) > > > how can i have the same in pg10 as i have had in pg 9.x? I went to section 37.4.8: https://www.postgresql.org/docs/10/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET I am not seeing a solution, but you might see something that would help you. > > I need it to date type to... if possible. > > > -- > Att. > Márcio A. Sepp > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Monday, January 8, 2018, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
I am not seeing a solution, but you might see something that would help you.
There is no general solution. For the problem at hand I would union two generate_series(1,5) queries with a query_id column. Then I'd use row_number() over (order by query_id, series_num) to compute the column containing the values 1-10.
Alternatively, use the modulus operator (% 5) on 1-10 to generate the second column.
David J.
Márcio A. Sepp wrote: > how can i have the same in pg10 as i have had in pg 9.x? Move the function call to the FROM clause: select g, (g - 1) % 5 + 1 from generate_series(1, 10) g; > I need it to date type to... if possible. There is a generate_series() variant that can return dates (more precisely, timestamp with time zone). But what exactly would you like returned? -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
2018-01-09 11:01 GMT+01:00 Alvaro Herrera <alvherre@alvh.no-ip.org>: > Márcio A. Sepp wrote: > >> how can i have the same in pg10 as i have had in pg 9.x? > > Move the function call to the FROM clause: > > select g, (g - 1) % 5 + 1 from generate_series(1, 10) g; > >> I need it to date type to... if possible. > > There is a generate_series() variant that can return dates (more > precisely, timestamp with time zone). But what exactly would you like > returned? > > -- > Álvaro Herrera https://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > You can add a generated series as seconds, minutes, hours ... to a base timestamp. Yes, it's not an easy going expression, but I'd do it like this. -- Vincenzo Romano - NotOrAnd.IT Information Technologies -- NON QVIETIS MARIBVS NAVTA PERITVS
> > how can i have the same in pg10 as i have had in pg 9.x?
>
> Move the function call to the FROM clause:
>
> select g, (g - 1) % 5 + 1 from generate_series(1, 10) g;
thank you. That is exact what i need.
> > I need it to date type to... if possible.
>
> There is a generate_series() variant that can return dates (more
> precisely, timestamp with time zone). But what exactly would you like
> returned?
In the past i use querys like this to generate some timestamp field:
select generate_series (1, 10), generate_series('2018-01-01
10:00'::timestamp, '2018-01-02 10:00'::timestamp, '10 hours');
in some case I need to order the timestamp data field and in others I just
need an interval. So, in some moments an random data already help me and in
others it is necessary to have both fields aligned.
thanks
Márcio A. Sepp wrote:
> > There is a generate_series() variant that can return dates (more
> > precisely, timestamp with time zone). But what exactly would you like
> > returned?
>
> In the past i use querys like this to generate some timestamp field:
> select generate_series (1, 10), generate_series('2018-01-01
> 10:00'::timestamp, '2018-01-02 10:00'::timestamp, '10 hours');
>
>
> in some case I need to order the timestamp data field and in others I just
> need an interval. So, in some moments an random data already help me and in
> others it is necessary to have both fields aligned.
Maybe something like this
select *
from generate_series(date '2018-01-01 10:00', '2018-01-02 10:00', '10 hours')
with ordinality;
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, Jan 8, 2018 at 11:19 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 01/08/2018 05:25 PM, Márcio A. Sepp wrote: >> >> >> Hi, >> >> >> In pg10 generation series doesn't work like in 9.5. >> Ex. in 9.5: >> z=# select generate_series(1, 10), generate_series(1, 5); >> generate_series | generate_series >> -----------------+----------------- >> 1 | 1 >> 2 | 2 >> 3 | 3 >> 4 | 4 >> 5 | 5 >> 6 | 1 >> 7 | 2 >> 8 | 3 >> 9 | 4 >> 10 | 5 >> (10 registros) >> >> >> so, in version 10 the same sql show different result set. > > > The reason why: > > https://www.postgresql.org/docs/10/static/release-10.html#idm46428657945600 > > Change the implementation of set-returning functions appearing in a query's > SELECT list (Andres Freund) An interesting (and good) side effect of this change is that this query: select generate_series(1, nextval('s')), generate_series(1, nextval('s')); ...now terminates. merlin
On Tue, Jan 9, 2018 at 10:14 AM, Márcio A. Sepp
<marcio@zyontecnologia.com.br> wrote:
>
>> > how can i have the same in pg10 as i have had in pg 9.x?
>>
>> Move the function call to the FROM clause:
>>
>> select g, (g - 1) % 5 + 1 from generate_series(1, 10) g;
>
> thank you. That is exact what i need.
>
>
>> > I need it to date type to... if possible.
>>
>> There is a generate_series() variant that can return dates (more
>> precisely, timestamp with time zone). But what exactly would you like
>> returned?
>
> In the past i use querys like this to generate some timestamp field:
> select generate_series (1, 10), generate_series('2018-01-01
> 10:00'::timestamp, '2018-01-02 10:00'::timestamp, '10 hours');
With the old behavior you're lucky this ever worked at all. Doing
this kind of stuff, you want to write it with a single generate_series
driver if you can or as a cross product:
select
a, b,c
from generate_series(1,3) a
cross join generate_series(1,4) b
cross join generate_series(1,2) c;
The old behavior presented 'least common multiple' which was very
surprising in that it sometimes worked like cross product but
sometimes didn't depending on specific numbers chosen.
merlin
> >> > how can i have the same in pg10 as i have had in pg 9.x?
> >>
> >> Move the function call to the FROM clause:
> >>
> >> select g, (g - 1) % 5 + 1 from generate_series(1, 10) g;
> >
> > thank you. That is exact what i need.
> >
> >
> >> > I need it to date type to... if possible.
> >>
> >> There is a generate_series() variant that can return dates (more
> >> precisely, timestamp with time zone). But what exactly would you
> >> like returned?
> >
> > In the past i use querys like this to generate some timestamp field:
> > select generate_series (1, 10), generate_series('2018-01-01
> > 10:00'::timestamp, '2018-01-02 10:00'::timestamp, '10 hours');
>
> With the old behavior you're lucky this ever worked at all. Doing this
> kind of stuff, you want to write it with a single generate_series
> driver if you can or as a cross product:
>
> select
> a, b,c
> from generate_series(1,3) a
> cross join generate_series(1,4) b
> cross join generate_series(1,2) c;
Exactly what I need. Thank you so much!!!