On Tue, May 26, 2015 at 8:45 AM, Shekar Tippur <ctippur@gmail.com> wrote:
This is what I am trying:
WITH x AS
(INSERT INTO industry (name,abbr,description,cr_date,last_upd)
VALUES ('df','','',now(),now()) returning id) insert into sector (name,description,cr_date,last_upd,industry_id) select 's1','',now(),now(),id from x;
I get a error:
ERROR: insert or update on table "sector" violates foreign key constraint "sector_id_fkey"
DETAIL: Key (id)=(394) is not present in table "industry".
If I execute the insert individually, I am able to insert a record. Wonder what I am doing wrong.
I have been stuck with this issue for over 24 hours. Appreciate any help.
It is not possible to accomplish your goal using a CTE. From the point of view of both tables the data they can see is what was present before the statement began.
The more usual way to accomplish this is the write a pl/pgsql function with two statements and passing the ID between them using an intermediate variable.
David J.
>>>>>>>>>>>>>>>>>>>
I have tried that as well.
INSERT INTO industry (name,abbr,description,cr_date,last_upd) VALUES (NEW.industry,'','',now(),now()) returning id into industry_id;
industry_id := (select industry_id from industry where name = 'NEW.industry');
raise notice 'industry id is %', industry_id;
INSERT INTO sector (name,description,cr_date,last_upd,industry_id) VALUES (NEW.sector,'',now(),now(),industry_id) returning id into sector_id;
-- I get a new industry ID but a new row is not inserted. I am guessing this is the case because it takes all the transactions as atomic. As a result, I get a foreign key violation.
>>>>>>>>>>>>>>>>>>
If you are using a trigger you should also provide the relevant CREATE TRIGGER statement...
In fact, you really you supply a self-contained example.