Re: Re: Explicitly inserting NULL values into NOT NULL DEFAULT 0 columns

On Oct 2, 2012, at 12:01, Etienne Rouxel <rouxel(dot)etienne(at)gmail(dot)com> wrote:

> Hi
> I have the same "problem" as Tanmay Patel.
>
> SELECT * FROM version();
> returns :
> "PostgreSQL 8.4.12 on i386-apple-darwin, compiled by GCC
> i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370),
> 32-bit"
>
> Here is my simplified code :
>
> CREATE TABLE public.mytable (
> refno int NOT NULL DEFAULT 1
> );
>
> CREATE FUNCTION public.mytable_insert_refno() RETURNS trigger AS $BODY$
> BEGIN
> NEW.refno := 123;
> RETURN NEW;
> END;
> $BODY$
> LANGUAGE plpgsql
> VOLATILE;
>
> CREATE TRIGGER mytable_insert_refno
> BEFORE INSERT
> ON public.mytable
> FOR EACH ROW
> EXECUTE PROCEDURE public.mytable_insert_refno();
>
> INSERT INTO public.mytable (refno) VALUES (NULL) RETURNING *;
>
> As we can see when running this code, the trigger prevent the NULL value to
> reach the NOT NULL constraint check and a relation is successfully inserted
> in the table with value 123.
>
> I managed to realize this behavior when unit testing my database, the
> question I am asking myself is : in which order are the constraints checked?
> Is this order guarantee or not?
>
>

Resurrecting a year-old thread is generally poor form, especially since you didn't quote any context and the question posed is quite a bit different than the original thread.

That said.

NULL and CHECK constrains occur after all before triggers and before all after triggers. More generally constraints are enforced on the final data which can be altered by before triggers (and triggers are not constraints). Since after triggers cannot alter the data anyway they fire last and only if the data constraints are met.

Multiple triggers are run in alphabetical order.

David J.