Re: [HACKERS] Bug: timestamp without time zone column allows timestamptz default

[Tom Lane]

David Fetter  writes:
> CREATE TABLE bar(t TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP);

> This is a foot gun we need not have.

Yes, we do need to have it.

(1) if we remove the implicit cast from timestamptz to timestamp, the
villagers will be on our doorsteps with the usual equipment.  I'm still
hearing bitching about the 8.3 implicit-cast-ectomy, which took away
only casts far less defensible than this one.

(2) you have not actually demonstrated an unacceptable consequence
of the above coding.  I will grant that using "localtimestamp" is
a cleaner and more efficient approach, but:

regression=# create table tt (f1 timestamp default current_timestamp,
regression(# f2 timestamp default localtimestamp);
CREATE TABLE
regression=# insert into tt default values;
INSERT 0 1
regression=# table tt;
f1 |f2 
---+---
 2017-03-14 11:13:31.78774 | 2017-03-14 11:13:31.78774
(1 row)

Looks to me like you get the same results after zone rotation happens.
(The same is true when the column type is timestamptz, btw.)

regards, tom lane


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

[HACKERS] Bug: timestamp without time zone column allows timestamptz default

[David Fetter]

Folks,

What happens now:

CREATE TABLE bar(id int DEFAULT 'a');
ERROR:  invalid input syntax for integer: "a"

Good so far.  Now with a different data type.

CREATE TABLE bar(t TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE

This is a foot gun we need not have.  TIMESTAMP WITHOUT TIME ZONE is
already a foot gun for the vast majority of real world cases.  We
should not be making it worse by papering this over.

Ideally, this would look more like:

CREATE TABLE bar(t TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP);
ERROR:  invalid input syntax for timestamp without time zone: 
current_timestamp
HINT: perhaps you meant to use timestamp with time zone.

Best,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


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