Hi Thom,
On 2014-02-08 22:26:59 +0000, Thom Brown wrote:
> Got a question about ranges and arrays usage with timestamps... why
> are quotes added to these?
>
> timestamptz (no quotes with input or output):
> table "a": INSERT: moo[timestamptz]:2014-02-08 22:09:33+00
>
> tstzrange (no quotes with input, but quotes with output):
> table "b": INSERT: moo[tstzrange]:["2014-02-08
> 13:45:22+00","2014-02-08 14:45:42+00")
>
> timestamptz[] (no quotes with input, but quotes with output):
> table "c": INSERT: moo[_timestamptz]:{"2010-01-01
> 13:45:22+00","2010-01-03 14:45:42+00"}
>
> tstzrange[] (one set of quotes with input, two sets of quotes with
> output, one set of which are escaped):
> table "d": INSERT: moo[_tstzrange]:{"(\"2014-02-08
> 13:45:22+00\",\"2014-02-08 13:45:42+00\"]","[\"2014-02-07
> 10:12:19+00\",\"2014-02-07 13:51:16+00\"]"}
The test_decoding output plugin just uses the default text output
functions for all types, other plugins could do differently. I.e. in all
these cases a SELECT, COPY, pg_dump will also include those quotes.
E.g.
postgres=# SELECT ARRAY[tstzrange(NOW(), NOW() + interval '1 day')];
will format it's output similarly:{"[\"2014-02-08 23:44:19.82007+01\",\"2014-02-09 23:44:19.82007+01\")"}
(1 row)
Does that answer make sense?
Greetings,
Andres Freund
-- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services
