PostgreSQL – Valid String Representation of tstzrange[]

postgresqlpostgresql-9.4

I am trying to find string representation of tstzrange[] type (array of timestamp ranges):

select '{[2014-01-01,2015-01-01]}'::tstzrange[];
select '{''[2014-01-01,2015-01-01]''}'::tstzrange[];
select '{[''2014-01-01'',''2015-01-01'']}'::tstzrange[];

But each of queries above throws malformed range literal error.

I know that I can use array[tstzrange('2014-01-01','2015-01-01','[]')] but what I need is a string representation.

Best Answer

The thing is that here you have to combine two literals: '{}' for the array and '[...,...]' for the range type.

In all these cases, the easiest is just getting back the output from the system itself:

SELECT array[tstzrange('2014-01-01','2015-01-01','[]')];
                            array                            
─────────────────────────────────────────────────────────────
 {"[\"2014-01-01 00:00:00+01\",\"2015-01-01 00:00:00+01\"]"}

Now you simply wrap this into quotes, and there you are.

There is, however, a simpler way, which usually works: just double-quote the inner literal. See:

select '{"[2014-01-01,2015-01-01]"}'::tstzrange[];
                          tstzrange                          
─────────────────────────────────────────────────────────────
 {"[\"2014-01-01 00:00:00+01\",\"2015-01-01 00:00:00+01\"]"}

Note that the output is the same as above.

Related Question