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

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?

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Explicitly-inserting-NULL-values-into-NOT-NULL-DEFAULT-0-columns-tp5012482p5726334.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.