How to insert dates as date array in postgresql-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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