Re: Bug with views and defaults

"Mark Hollomon" <mhh(at)nortelnetworks(dot)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