Thread: constraints evaluate 'now' immediately

Roger Wernersson (rw@mindark.com) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
constraints evaluate 'now' immediately

Long Description
I run Postgres 6.5.3 on Alpha/Linux.

I would like a constraint stating that a column may not have a date in the future:

CHECK(my_date <= 'now')

but I think it stores the time of constraint creation.


Sample Code
CREATE TABLE test (
    my_date timestamp NOT NULL,
    CONSTRAINT test_c_my_date CHECK (my_date <= 'now'));

--wait a second then

INSERT INTO test VALUES ('now');

-- fails


No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> I would like a constraint stating that a column may not have a date in the future:
> CHECK(my_date <= 'now')
> but I think it stores the time of constraint creation.

Yes, because 'now' is evaluated when the constant is parsed.  Try
    CHECK(my_date <= now())
instead.

            regards, tom lane