Thread: [HACKERS] Bug: timestamp without time zone column allows timestamptz default
[HACKERS] Bug: timestamp without time zone column allows timestamptz default
From
David Fetter
Date:
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 timestampwithout time zone: current_timestamp HINT: perhaps you meant to use timestamp with time zone. Best, David. -- David Fetter <david(at)fetter(dot)org> 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
Re: [HACKERS] Bug: timestamp without time zone column allows timestamptz default
From
Tom Lane
Date:
David Fetter <david@fetter.org> 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