# postgreSQL generate\_series in v9.6 vs 12.8

**URL:** <https://forum.inductiveautomation.com/t/postgresql-generate-series-in-v9-6-vs-12-8/52815>\
**Category:** Ignition\
**Created:** [November 7, 2021, 10:49am UTC](https://forum.inductiveautomation.com/t/postgresql-generate-series-in-v9-6-vs-12-8/52815 "2021-11-07T10:49:23Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\
**Post date:** [November 7, 2021, 10:49am UTC](https://forum.inductiveautomation.com/t/postgresql-generate-series-in-v9-6-vs-12-8/52815/1 "2021-11-07T10:49:23Z")

</div>

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_9_6](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/e/ee88c012147ce4abdd9bec5ab92c2576d8cd0d13.png)  
**postgreSQL v12.8** :  
 ![postgreSQL_12_8](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/3/e3753e2353a7576f4d43ce78ba784ab557b0c2de.png)  
I have this query in my Vision window which works in PostgreSQL v9.6 and now in v12.8 doesn’t…  
**postgreSQL v9.6**  
 ![Screenshot_24](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/9/19b6896144086ba11ef6ce65bece9d1ba2e4ed05.png)  
**postgreSQL v12.8**  
 ![Screenshot_25](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/a/6af049c0efee26f426360529625c061d8f55588b.png)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 7, 2021, 12:54pm UTC](https://forum.inductiveautomation.com/t/postgresql-generate-series-in-v9-6-vs-12-8/52815/2 "2021-11-07T12:54:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\
**Post date:** [November 7, 2021, 3:09pm UTC](https://forum.inductiveautomation.com/t/postgresql-generate-series-in-v9-6-vs-12-8/52815/3 "2021-11-07T15:09:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 7, 2021, 3:40pm UTC](https://forum.inductiveautomation.com/t/postgresql-generate-series-in-v9-6-vs-12-8/52815/4 "2021-11-07T15:40:08Z")

</div>

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

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

```

Untested…

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 7, 2021, 3:50pm UTC](https://forum.inductiveautomation.com/t/postgresql-generate-series-in-v9-6-vs-12-8/52815/5 "2021-11-07T15:50:14Z")

</div>

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

> [@Script for periodically changing shift time](https://forum.inductiveautomation.com/t/script-for-periodically-changing-shift-time/51058/3):
>
> 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…

---

<div class="post-metadata">

**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\
**Post date:** [November 7, 2021, 3:52pm UTC](https://forum.inductiveautomation.com/t/postgresql-generate-series-in-v9-6-vs-12-8/52815/6 "2021-11-07T15:52:52Z")

</div>

This IS the query:

```auto
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:

 ![Screenshot_26](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/4/44a1b2157e9a3659cb1c633d5987a0a4f4aa0219.png)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 7, 2021, 4:00pm UTC](https://forum.inductiveautomation.com/t/postgresql-generate-series-in-v9-6-vs-12-8/52815/7 "2021-11-07T16:00:34Z")

</div>

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).

---

<div class="post-metadata">

**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\
**Post date:** [November 7, 2021, 4:08pm UTC](https://forum.inductiveautomation.com/t/postgresql-generate-series-in-v9-6-vs-12-8/52815/8 "2021-11-07T16:08:27Z")

</div>

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

```auto
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

```

 ![postgreSQL_9_6](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/e/ee88c012147ce4abdd9bec5ab92c2576d8cd0d13.png)

is not working anymore.

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

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 7, 2021, 4:08pm UTC](https://forum.inductiveautomation.com/t/postgresql-generate-series-in-v9-6-vs-12-8/52815/9 "2021-11-07T16:08:56Z")

</div>

Try this:

```auto
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;

```

---

<div class="post-metadata">

**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\
**Post date:** [November 7, 2021, 4:30pm UTC](https://forum.inductiveautomation.com/t/postgresql-generate-series-in-v9-6-vs-12-8/52815/10 "2021-11-07T16:30:01Z")

</div>

Great. Thank you. 👍

```auto
/*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;

```

 ![Screenshot_27](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/d/9dbddab02b3300b3e60b100c2df1b9f039cd13a8.png)  
My problem was ‘8 hour’ interval in v9.6, and ‘1 day’ in v12…