Inserting a generate_series select date statement …

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?

Answer

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

Inserting a generate_series select date statement into an array column

Advertisement

Answer