Skip site navigation (1) Skip section navigation (2)

Re: Null values detected as 0 value

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Vaduvoiu Tiberiu <vaduvoiutibi(at)yahoo(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Null values detected as 0 value
Date: 2010-11-22 14:43:07
Message-ID: 25608.1290436987@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Vaduvoiu Tiberiu <vaduvoiutibi(at)yahoo(dot)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

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2010-11-22 15:09:46
Subject: Re: Could not open relation with OID (table partitioning issue?)
Previous:From: Vaduvoiu TiberiuDate: 2010-11-22 12:59:34
Subject: Null values detected as 0 value

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group