Thread: [BUGS] BUG #14842: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIMEZONEshould not be comparable
[BUGS] BUG #14842: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIMEZONEshould not be comparable
From
edpeur@gmail.com
Date:
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
Re: [BUGS] BUG #14842: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUTTIMEZONE should not be comparable
From
"David G. Johnston"
Date:
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.
Re: [BUGS] BUG #14842: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIMEZONE should not be comparable
From
Tom Lane
Date:
"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
Re: [BUGS] BUG #14842: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUTTIMEZONE should not be comparable
From
Eduardo Pérez Ureta
Date:
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>:
"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
Re: [BUGS] BUG #14842: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUTTIMEZONE should not be comparable
From
"David G. Johnston"
Date:
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.
Re: [BUGS] BUG #14842: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUTTIMEZONE should not be comparable
From
John R Pierce
Date:
On 10/3/2017 2:54 PM, David G. Johnston 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
Re: [BUGS] BUG #14842: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUTTIMEZONE should not be comparable
From
Francisco Olarte
Date:
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