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

If I insert a NULL value explicitly into a column declared to be NOT NULL DEFAULT 0 in postgreSQL 8.4 the column ends up with the default value. If I do the same in postgreSQL 9.0 I get an error about how I am inserting a null value into a NOT NULL column.

i.e.: insert into table1 (column1, column2) values (0, NULL); where column2 is of type integer with attributes NOT NULL DEFAULT 0

In both cases if I just don't mention the column with these attributes the value stored is the default value.

i.e.: insert into table1(column1) values (0); where column2 is of type integer with attributes NOT NULL DEFAULT 0

I looked through all the release notes between the versions in question and can find nothing mentioning this change. When did this change occur, and can I choose to keep the behavior as it was in postgreSQL 8.4?

Thanks,

Tanmay

Tanmay Patel <tan.patel.may@gmail.com> writes:
> If I insert a NULL value explicitly into a column declared to be NOT NULL
> DEFAULT 0 in postgreSQL 8.4 the column ends up with the default value. If I
> do the same in postgreSQL 9.0 I get an error about how I am inserting a
> null value into a NOT NULL column.

I'm sorry, but you're quite mistaken about the behavior of 8.4.  Every
version of Postgres would reject this; no version has ever considered an
explicit specification of NULL to be an invitation to insert the
column's default value instead.  (I have heard that mysql acts that way,
though.)

            regards, tom lane

On Mon, Nov 21, 2011 at 5:27 PM, Tanmay Patel <tan.patel.may@gmail.com> wrote:
> If I insert a NULL value explicitly into a column declared to be NOT NULL
> DEFAULT 0 in postgreSQL 8.4 the column ends up with the default value. If I
> do the same in postgreSQL 9.0 I get an error about how I am inserting a null
> value into a NOT NULL column.

As Tom pointed out you are mistaken.  That's a MySQLism.  If you want
to insert defaults, use the DEFAULT keyword in place of where you're
trying to put NULL.

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.

On Oct 2, 2012, at 12:01, Etienne Rouxel <rouxel.etienne@gmail.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
posedis 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
areenforced on the final data which can be altered by before triggers (and triggers are not constraints).  Since after
triggerscannot alter the data anyway they fire last and only if the data constraints are met. 

Multiple triggers are run in alphabetical order.

David J.