Обсуждение: [BUGS] BUG #14842: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIMEZONEshould not be comparable

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


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

"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

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.

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