2

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
3
  • 1
    Is there a particular reason that you are using [ and )? Create a db<>fiddle that demonstrates your scenario – Lennart Dec 30 '20 at 0:16
  • Hmm, the double quotes do work for me (though the date format has to be changed): dbfiddle.uk/… – sticky bit Dec 30 '20 at 6:58
  • Your Postgres version and the client and version in use would be essential to this question. – Erwin Brandstetter Jan 2 at 19:02
0

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.