Thread: ERROR: AlterTableAddConstraint:

Hi:

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?

> 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 :)