Re: abnormal behaviour of a CHECK CONSTRAINT with a regular

[Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>]

On Tue, 10 Feb 2004, Daniel Struck wrote:

> retrovirology=# INSERT INTO test_reg1 (date_user) VALUES ('111.1999');
> ERROR:  invalid input syntax for type date: "111.1999"
> CONTEXT:  PL/pgSQL function "function_test_reg1" line 8 at assignment
> retrovirology=# INSERT INTO test_reg1 (date_user) VALUES ('11:1999');
> ERROR:  invalid input syntax for type date: "15.11:1999"
> CONTEXT:  PL/pgSQL function "function_test_reg1" line 12 at assignment
> retrovirology=#
>
> This values do pass NOW the CHECK CONSTRAINT ???? and are injected in
> the trigger function. Normally the trigger shouldn't m have an influence
> an the CHECK CONSTRAINT.

Wrong order.  Before triggers happen before the table check constraint is
checked (since the triggers can change the data). IIRC, you can currently
have a constraint that is checked before that currently by making the
column type be a domain that has the constraint since the value gets
coerced to the column type (and checked) when making the row value for the
trigger.