Inserting date range arrays daterange[]

Question

I'm trying to insert the value of an attribute of a tuple, which would be an array of date ranges, i.e. a daterange[], in a previous tuple accepted the value:

{"[2013-05-08,2018-03-22)"}

But now I would like to insert this array, with more than one range:

{"[19-03-2018,29-12-2020)","[21-06-2018,29-12-2020)", "[17-07-2018,29-12-2020)", 
        "[18-04-2018,30-07-2018]", "[01-08-2019,19-11-2019]"}

I have tried all possible solutions,putting the outer quotes

 '{"[19-03-2018,29-12-2020)","[21-06-2018,29-12-2020)", "[17-07-2018,29-12-2020)", 
        "[18-04-2018,30-07-2018]", "[01-08-2019,19-11-2019]"}'

Putting the internal single quotes instead of the double ones, but nothing works.

Gives me the error:

argument formats can't be mixed

Answer 1

This would be legal syntax - using ISO format for dates (YYYY-MM-DD), which is always a good idea as it does not depend on the current setting of datestyle. Your example only works with datestyle = 'DMY'.

 '{"[2018-03-19,2020-12-29)","[2018-06-21,2020-12-29)"}'

You can always ask Postgres to show you:

test=# SELECT ARRAY ['[2018-03-19,2020-12-29)'::daterange ,'[2018-06-21,2020-12-29)'];
                         array                         
-------------------------------------------------------
 {"[2018-03-19,2020-12-29)","[2018-06-21,2020-12-29)"}
(1 row)

The error msg you report does not seem to be generated by Postgres.

argument formats can't be mixed

Are you, by any chance, using an outdated version of pgAdmin III? See:

• pgAdmin argument formats can't be mixed