How to insert dates as date array in postgresql

Question

I am trying to add dates into an array from a select query.

I am getting this error:

SQL Error [22P02]: ERROR: malformed array literal: "2021-04-02"

declare dateval date[];
begin 
select days into dateval from holidays where days between '2021-01-01' and '2021-12-31' and city='NY';

then I need to compare this array in if statement my date variable in this dateval array do something or go to else like that

user330315 · Accepted Answer · 2021-11-18 07:07:08Z

1

You need to aggregate them into an array in order to be able to store them into one:

select array_agg(days) 
  into dateval
from ...

answered Nov 18, 2021 at 7:07

user330315

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

Comments

Stefanov.sm · Accepted Answer · 2021-11-18 15:16:03Z

0

Maybe you do not need an array but use exists and a subquery in your if condition.

if my_date_var between '2021-01-01' and '2021-12-31' and exists 
(
  select from holidays 
  where my_date_var = days 
  and city='NY'
) then ...

or use in (probably less efficient)

if my_date_var in
(
  select days from holidays 
  where days between '2021-01-01' and '2021-12-31' 
  and city='NY'
) then ...

answered Nov 18, 2021 at 15:07

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges