Tom Lane wrote:
I've started reviewing this patch for commit, and I find myself a bit
disturbed by its compatibility properties. The SQL_STANDARD output
style is simply ambiguous: what is meant by
-1 1:00:00
? What you get from that will depend on the intervalstyle setting at
the recipient.
Nope. The SQL Standard style avoids the ambiguity by following
the SQL Standard's rules when the input value complied with the
standard's restrictions on intervals.
For example - given the sql standard compliant value of negative
one days and negative one hours you get "-1 1;00:00".
If you give it a non-sql-standard-compliant value like negative
one days plus one hours it will force outputting all the signs
both positive and negative:
regression=# select interval '-1 days +1 hours';
interval
------------------
+0-0 -1 +1:00:00
(1 row)
I agree that there's an ambiguity on input - in much the same way
that date order can affect ambiguous inputs.
Either of the traditional Postgres styles are
non-ambiguous and will be interpreted correctly regardless of receiver's
intervalstyle --- in particular, Postgres mode always puts an explicit
sign on the time part if the days or months part was negative. What
this means is that SQL_STANDARD mode is unsafe for dumping data, and
So long as the SQL Standard style is chosen both on dumping and loading,
I think it will preserve any values given to it.
*pg_dump had better force Postgres mode*. We can certainly do that with
a couple more lines added to the patch, but it's a bit troublesome that
we are boxed into using a nonstandard dump-data format until forever.
I don't immediately see any way around that, though. Anyone have a
bright idea?
Are you concerned about someone dumping in SQL_STANDARD mode and
then importing in POSTGRES mode? If so, how's the similar case
handled with date order?
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
