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.