Обсуждение: 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
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.