POSTGRES declaring variable containing date array in functions

Question

I am trying to declare a date array.

I have tried this:

DECLARE
dateVal DATE[] := ['2018-01-01','2018-02-01'];

I get:

ERROR:  syntax error at or near "["

How do I properly declare it?

Answer 1

You can use ARRAY keyword:

create table tbl (dateval date[]);
insert into tbl values(array ['20180101','20180102'::date]);

Or in a block of code:

do $$
declare 
    dateval date[];
begin
    dateval := array ['20180101','20180102'::date];
end;
$$;

Or cast the expression as an array of dates.

do $$
declare 
    dateval date[];
begin
    dateval := '{2015-4-12, 2015-4-19}'::date[];
end;
$$;

db<>fiddle here

Answer 2

Assign the variable at declaration time if it's known at declaration time. Else it's initialized with NULL and you need another statement to assign it. A small waste of code and performance.

DO
$$
DECLARE
   dateval date[] := '{2015-4-12, 2015-4-19}';
BEGIN
   -- do something
END
$$;

You just need the right syntax for either an array literal or an ARRAY constructor.

The string literal above needs no explicit cast, since the type is defined in the assignment implicitly.

Related:

• Array of strings when updating a field