Bug with views and defaults

"Mark Hollomon" <mhh@nortelnetworks.com> writes:

CREATE TABLE foo (
name TEXT,
type CHAR(1),
when_added TIMESTAMP DEFAULT 'now'
);

CREATE VIEW mytype AS
SELECT name, when_added FROM foo WHERE type = 'M';

CREATE RULE mytype_insert AS
ON INSERT TO mytype DO INSTEAD
INSERT INTO foo (name, type) VALUES (NEW.name, 'M');

Inserting directly into foo sets when_added to the current time.
Inserting through the view sets it to what looks like the time of
view creation.

This is a known and not readily fixable problem. It's far safer
to write the default for a timestamp column as now(), rather than
relying on a string literal not getting coerced to timestamp form
too soon. See
http://www.postgresql.org/mhonarc/pgsql-hackers/1999-10/msg00036.html

BTW, Bruce: it probably would be wise to have the FAQ's item 4.22
recommend now() and nothing else. 'now' has nothing much to recommend
it and there are still pitfalls like this one.

regards, tom lane

This is a known and not readily fixable problem. It's far safer
to write the default for a timestamp column as now(), rather than
relying on a string literal not getting coerced to timestamp form
too soon. See
http://www.postgresql.org/mhonarc/pgsql-hackers/1999-10/msg00036.html

BTW, Bruce: it probably would be wise to have the FAQ's item 4.22
recommend now() and nothing else. 'now' has nothing much to recommend
it and there are still pitfalls like this one.

regards, tom lane

TODO updated.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026