Re: Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle

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?