Inserting a generate_series select date statement into an array column

Question

I have a Postgres table with a schema that looks like this -

                              Table "public.nav_due_dates"
  Column  |  Type   | Collation | Nullable |                    Default                    
----------+---------+-----------+----------+-----------------------------------------------
 nav_id   | integer |           | not null | nextval('nav_due_dates_nav_id_seq'::regclass)
 fund_id  | integer |           |          | 
 listdate | date[]  |           |          |

I have a SELECT statement that will generate all the days of the year... and eventually after joining against a weekends and holiday table I want filter out all the business days to insert those dates values into the listdate date[] array column. At the moment I'm using the below to generate rows with all the days of the year:

SELECT i::date from generate_series('2021-01-01', 
 '2021-12-31', '1 day'::interval) i;

So far I've tried to do the insert like so -

INSERT INTO nav_due_dates(fund_id, listdate) VALUES (100, ( 
  SELECT i::date from generate_series('2021-01-01', 
  '2021-12-31', '1 day'::interval) i)::date[] )
;

And this is the error it gives me -

ERROR:  cannot cast type date to date[]
LINE 3:   '2021-12-31', '1 day'::interval) i)::date[] )
                                             ^

Is there a good way to do this?

user330315 · Accepted Answer · 2021-04-13 15:13:37Z

3

You need to aggregate the values into an array, either using array_agg():

INSERT INTO nav_due_dates(fund_id, listdate) 
VALUES (100, (SELECT array_agg(i::date) from generate_series('2021-01-01','2021-12-31', '1 day'::interval) i) )
;

Or using the array() constructor:

INSERT INTO nav_due_dates(fund_id, listdate) 
VALUES (100, array(SELECT i::date from generate_series('2021-01-01','2021-12-31', '1 day'::interval) i) )
;

answered Apr 13, 2021 at 15:13

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

animusdx Over a year ago

Thank you so much! Before you posted the answer I had found out about array_agg but I couldn't seem to get the syntax correct!

Collectives™ on Stack Overflow

Inserting a generate_series select date statement into an array column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related