Null values detected as 0 value

From: Vaduvoiu Tiberiu <vaduvoiutibi(at)yahoo(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Null values detected as 0 value
Date: 2010-11-22 12:59:34
Message-ID: 980128.74065.qm@web51301.mail.re2.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2010-11-22 14:43:07 Re: Null values detected as 0 value
Previous Message Matthew Walden 2010-11-22 12:10:55 Re: Help with date query