ERROR: AlterTableAddConstraint:

I needed to put some data in to a table using \copy
The table should look like:

CREATE TABLE AA (t timestamp default current_timestamp
CHECK (t = current_timestamp));

but in order to be able to load in the data, I had to
create the table without the constraint first. Now
I want to add in the constraint. Here is my trouble:

test=# create table a (t timestamp);
CREATE
test=# alter table a add check (t = current_timestamp);
ALTER
test=# create table b (t timestamp);
CREATE
test=# insert into b values (current_timestamp+'1 day');
INSERT 21076 1
test=# insert into a values (current_timestamp+'1 day');
ERROR: ExecAppend: rejected due to CHECK constraint $1
test=# alter table b add check (t = current_timestamp);
ERROR: AlterTableAddConstraint: rejected due to CHECK constraint <unnamed>

I tried a few things with DEFERRABLE and INITIALLY DEFERRED
but I cannot get the syntax right. Is this possible, or do I
have to hack around in the system tables to do this?

The problem is that there's already a row that doesn't match the
constraint. At the check time of the constraint (end of
statement since AFAIK we don't support deferrable check
constraints -- actually we check during statement, but...)
the constraint needs to be satisfied and it is not (since
there exists a row that doesn't meet the constraint).

I think you may really want a before insert/update trigger
and not a check constraint. Especially since the way I
read the spec "A table check constraint is satisfied if and only
if the specified <search condition> is not false for any row of
a table." would make the constraint fail unless *every* row
had current_timestamp (or NULL) at the check time (end of
statement or transaction) not just the changed rows.

missive@frontiernet.net (Lee Harr) writes:

The table should look like:

CREATE TABLE AA (t timestamp default current_timestamp
CHECK (t = current_timestamp));

I don't think this is possible or sensible. A constraint is an
assertion, it should be valid whenever you check it (with the
single exception that DEFERRED constraints don't have to hold
intra-transaction).

I think what you really want is an ON INSERT OR UPDATE trigger that sets
the value of t to current_timestamp (regardless of what the user tried
to put in the column). You don't need a constraint, and you don't even
need a default --- the default just represents a wasted function call,
if you do it this way.

regards, tom lane

On Wed, 8 Aug 2001 23:32:19 +0000 (UTC), Tom Lane <tgl@sss.pgh.pa.us> wrote:

missive@frontiernet.net (Lee Harr) writes:

The table should look like:

CREATE TABLE AA (t timestamp default current_timestamp
CHECK (t = current_timestamp));

I don't think this is possible or sensible. A constraint is an
assertion, it should be valid whenever you check it (with the
single exception that DEFERRED constraints don't have to hold
intra-transaction).

I think what you really want is an ON INSERT OR UPDATE trigger that sets
the value of t to current_timestamp (regardless of what the user tried
to put in the column). You don't need a constraint, and you don't even
need a default --- the default just represents a wasted function call,
if you do it this way.

regards, tom lane

Ok, thank you.

I misunderstood the whole CHECK thing. I thought it was actually creating
a trigger for you, kind of like SERIAL or PRIMARY KEY creates extra stuff
automatically.

Guess I get to learn how to make a trigger :)