BUG #14842: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIMEZONE should not be comparable

On Tue, Oct 3, 2017 at 12:52 PM, <edpeur@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 14842
Logged by: Eduardo Perez
Email address: edpeur@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system: All
Description:

Currently you can do:
CREATE TABLE t1 (ts TIMESTAMP WITH TIME ZONE NOT NULL,tr TIMESTAMP WITHOUT
TIME ZONE NOT NULL);
INSERT INTO t1 (ts,tr) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
SELECT * FROM t1 WHERE ts=tr;

But it should fail with:
ERROR: operator does not exist: timestamp with time zone = timestamp
without
time zone
Also CURRENT_TIMESTAMP should fail to be inserted into a TIMESTAMP WITHOUT
TIME ZONE column
ERROR: column "tr" is of type timestamp without time zone but expression
is
of type timestamp with time zone

​This is working as intended and given the amount of pain removing implicit
casts​ generally involves it is unlikely to change.

I was curious whether something like "ALTER CAST ... NO IMPLICIT" would
work but alas no such feature presently exists.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Tue, Oct 3, 2017 at 12:52 PM, <edpeur@gmail.com> wrote:

Currently you can do:
CREATE TABLE t1 (ts TIMESTAMP WITH TIME ZONE NOT NULL,tr TIMESTAMP WITHOUT
TIME ZONE NOT NULL);
INSERT INTO t1 (ts,tr) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
SELECT * FROM t1 WHERE ts=tr;

But it should fail with:
ERROR: operator does not exist: timestamp with time zone = timestamp
without time zone
Also CURRENT_TIMESTAMP should fail to be inserted into a TIMESTAMP WITHOUT
TIME ZONE column
ERROR: column "tr" is of type timestamp without time zone but expression
is of type timestamp with time zone

​This is working as intended and given the amount of pain removing implicit
casts​ generally involves it is unlikely to change.

Not to mention that the SQL standard requires these implicit casts to
exist.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

I did not know that the SQL standard was so broken. Maybe it is time to
consider only following the SQL standard in its non-broken parts, like
other SQL databases do.

2017-10-03 20:38 GMT+00:00 Tom Lane <tgl@sss.pgh.pa.us>:

On Tue, Oct 3, 2017 at 2:19 PM, Eduardo Pérez Ureta <edpeur@gmail.com>
wrote:

I did not know that the SQL standard was so broken. Maybe it is time to
consider only following the SQL standard in its non-broken parts, like
other SQL databases do.

​It has its quirks but the two you have fixated on seem easy enough to
avoid.

At this point we've already decided that being conforming is generally a
virtue; especially since I'd assume that at least some "other SQL
databases" also conform to these same rules. Deciding to become more
strict (as opposed to starting out that way) requires significant benefit.

​David J.​

On 10/3/2017 2:54 PM, David G. Johnston wrote:

On Tue, Oct 3, 2017 at 2:19 PM, Eduardo Pérez Ureta <edpeur@gmail.com
<mailto:edpeur@gmail.com>>wrote:

I did not know that the SQL standard was so broken. Maybe it is
time to consider only following the SQL standard in its non-broken
parts, like other SQL databases do.

​It has its quirks but the two you have fixated on seem easy enough to
avoid.

At this point we've already decided that being conforming is generally
a virtue; especially since I'd assume that at least some "other SQL
databases" also conform to these same rules.  Deciding to become more
strict (as opposed to starting out that way) requires significant benefit.

IMHO, at *most* postgres could issue warnings on usage of sketchy
constructs.     I think emphasizing the warning in the documentation is
sufficient, however.

--
john r pierce, recycling bits in santa cruz

Eduardo:

On Tue, Oct 3, 2017 at 11:19 PM, Eduardo Pérez Ureta <edpeur@gmail.com> wrote:

I did not know that the SQL standard was so broken. Maybe it is time to
consider only following the SQL standard in its non-broken parts, like other
SQL databases do.

Are you sure they ( other DB ) do it this way? I've not used too many
other databases recently, but what I remember is they deviated from
the standard not because it was broken, but because it was easier to
deviate than not.

Francisco Olarte.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs