> 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.
