postgreSQL generate_series in v9.6 vs 12.8

[zxcslo]

Can somebody shed some light on this:
Why the same query in PostgreSQL v9.6 works and in v12.8 doesn’t anymore:
postgreSQL v9.6

postgreSQL v12.8:

I have this query in my Vision window which works in PostgreSQL v9.6 and now in v12.8 doesn’t…
postgreSQL v9.6

postgreSQL v12.8

[pturmel]

generate_series is a set-returning function and should be in your from clause. There, you can explicitly join the two generated series.

[zxcslo]

Isn’t that what I have in my last 2 pictures?
But in 9.6 is working and in 12.8 is not…?

[pturmel]

I don’t see a FROM keyword. Hmm. I would expect something like this:

Select dates.generate_series as "date", shifts.generate_series as "shift"
From generate_series(..........) dates Outer Join generate_series(1, 3) shifts

Untested…

[pturmel]

BTW, did you see my comprehensive solution to the problem of varying shifts? Over here:

Script for periodically changing shift time

Hi Berkay, I like to solve this kind of problem with a smart database, like PostgreSQL, where I can define a pattern and repeat it with table generating functions. I tweaked one of my implementations to serve as an example: create table shift_schedules (shift_id serial, shift_name varchar(255), refdate date, repeat int, tzname varchar(255), primary key (shift_id)); insert into shift_schedules (shift_name, refdate, repeat, tzname) values ('Berkay Cinar 3wk', '2021-07-26', 21, 'America/Ne…

[zxcslo]

This IS the query:

SELECT a.datum, a.izmena, energijakosipoizmenah_new.dobri, energijakosipoizmenah_new.slabi, energijakosipoizmenah_new.porabaelektrike, energijakosipoizmenah_new.porabazraka
FROM (
	SELECT date(generate_series('2021-05-10 00:00:00'::timestamp, '2021-05-17 23:59:59','8 hour')) datum,
		generate_series(1, 3) izmena) a
LEFT OUTER JOIN energijakosipoizmenah_new ON date(a.datum) = date(energijakosipoizmenah_new.datum) AND a.izmena = energijakosipoizmenah_new.izmena;

and this is the result:

[pturmel]

You are putting your generate_series functions into the select list of the inner query. I didn’t think you could do that, except for the fact that postgres will attempt to execute the expression. The generate_series() function returns a table (set of rows) that has the single column “generate_series”. That you were getting anything seems to be almost accidental for how this function is supposed to be be used (in the FROM clause).

[zxcslo]

OK, I found on the internet that they changed the behavior of the generate_series in version 10.
That explains why this query:

SELECT date(generate_series('2021-05-10 00:00:00'::timestamp, '2021-05-17 23:59:59','8 hour')) date,
		generate_series(1, 3) shift

is not working anymore.

But now how can I get the same result in v12.8 as I did in v9.6?

[pturmel]

Try this:

SELECT a.datum, a.izmena, energijakosipoizmenah_new.dobri, energijakosipoizmenah_new.slabi, energijakosipoizmenah_new.porabaelektrike, energijakosipoizmenah_new.porabazraka
FROM (
	SELECT b.generate_series datum,
		c.generate_series izmena
	FROM generate_series('2021-05-10'::date, '2021-05-17'::date,'1 day') b,
		generate_series(1, 3) c
) a
LEFT OUTER JOIN energijakosipoizmenah_new ON a.datum = date(energijakosipoizmenah_new.datum) AND a.izmena = energijakosipoizmenah_new.izmena;

[zxcslo]

Great. Thank you.

/*THIS IS WORKING!	LIKE IN 9.6*/
SELECT a.datum, a.izmena, energijakosipoizmenah_new.dobri, energijakosipoizmenah_new.slabi, energijakosipoizmenah_new.porabaelektrike, energijakosipoizmenah_new.porabazraka
FROM (
	SELECT b::date datum,
		c izmena
	FROM generate_series('2021-05-10'::date, '2021-05-17'::date,'1 day') b,
		generate_series(1, 3) c
) a
LEFT OUTER JOIN energijakosipoizmenah_new ON a.datum = date(energijakosipoizmenah_new.datum) AND a.izmena = energijakosipoizmenah_new.izmena;

My problem was ‘8 hour’ interval in v9.6, and ‘1 day’ in v12…