Postgres Pro
Downloads
Home   >   mailing lists

Thread: Null values detected as 0 value

Null values detected as 0 value

From
Vaduvoiu Tiberiu
Date:
Weird stuff is happening to me today...I have a table and a view that has same fields as the table. The table(tb_network) has a field((parent_network_id) which is fk to itself. The view has a rule create for itself:

CREATE OR REPLACE RULE "Network_insert_unconditional" AS
    ON INSERT TO "view_network" DO INSTEAD  INSERT INTO tb_network (...,parent_network_id....)
  VALUES (....new.net_id_parent...)  RETURNING zxt_networks.network_id;

Problem is, when I perform an insert on the view like this:
INSERT INTO view_network (... parent_network_id....)
VALUES (....,NULL,....)

I always get " insert or update on table "tb_network" violates foreign key constraint ..DETAIL: Key (parent_network_id)=(0) is not present in table "tb_network". So even though in the insert query I pass NULL value, in the table, it tries to actually insert 0 instead of null. Has anybody had a similar problem? The constraint is declared like this:

ALTER TABLE tb_network
  ADD CONSTRAINT tb_network_tb_network FOREIGN KEY (parent_network_id)
      REFERENCES tb_network (network_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;

Also, if I execute a insert query on the table instead of the view, it works perfectly.


Re: Null values detected as 0 value

From
Tom Lane
Date:
Vaduvoiu Tiberiu <vaduvoiutibi@yahoo.com> writes:
> Weird stuff is happening to me today...I have a table and a view that has same fields as the table. The
table(tb_network)has a field((parent_network_id) which is fk to itself. The view has a rule create for itself:  
> CREATE OR REPLACE RULE "Network_insert_unconditional" AS
> ��� ON INSERT TO "view_network" DO INSTEAD� INSERT INTO tb_network (...,parent_network_id....)
> � VALUES (....new.net_id_parent...)� RETURNING zxt_networks.network_id;

> Problem is, when I perform an insert on the view like this:
> INSERT INTO view_network (... parent_network_id....)
> VALUES (....,NULL,....)

> I always get " insert or update on table "tb_network" violates foreign key constraint ..DETAIL: Key
(parent_network_id)=(0)is not present in table "tb_network". 

Well, you've omitted all the useful details, but this stuff is not
magic.  Either that ON INSERT rule is changing null to zero, or you
have a trigger on the table that is doing it, or there's something
wrong with the initial query (are you sure you put the null in the
column you thought you did?)

            regards, tom lane

Re: Null values detected as 0 value

From
Vaduvoiu Tiberiu
Date:
"Well, you've omitted all the useful details, but this stuff is not
magic.  Either that ON INSERT rule is changing null to zero, or you
have a trigger on the table that is doing it, or there's something
wrong with the initial query (are you sure you put the null in the
column you thought you did?)

It would be too much code to insert here, no triggers exist on either the view or the table, the only problem I think can be cause by the default value.
Is there a problem when setting the default values as null for a field that is a foreign key to it's own table?

I've tried adding the default value as:
ALTER TABLE network ALTER COLUMN parent_network SET DEFAULT NULL::integer; ( and also without ::integer or with ::bigint) but even though the query runs succesfully, when viewing the table with pgAdmin III, the default value does not appear as null, as on other fields. So my concern is that the default value might not work correctly and 0 is still retrieved as the default value.