Thread: Postgres CTE issues
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.
On Tue, May 26, 2015 at 9:00 AM, David G. Johnston <david.g.johnston@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.
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.
<div style="direction: ltr;font-family: Tahoma;color: #000000;font-size: 10pt;"><br /> > This is what I amtrying:<br /> ><br /> > WITH x AS <br /> ><br /> > (INSERT INTO industry (name,abbr,description,cr_date,last_upd)<br /> ><br /> > VALUES ('df','','',now(),now()) returning id) insertinto sector (name,description,cr_date,last_upd,industry_id) select 's1','',now(),now(),id from x;<br /> ><br />> I get a error:<br /> ><br /> > ERROR: insert or update on table "sector" violates foreignkey constraint "sector_id_fkey"<br /> ><br /> > DETAIL: Key (id)=(394) is not present in table "industry".<br/> ><br /> > If I execute the insert individually, I am able to insert a record. Wonder whatI am doing wrong.<br /><br /> Hello,<br /> Defining your FK as deferrable initially deferred should help here.<br />regards,<br /><br /> Marc Mamin</div>
create table A (
var1 varchar(40),
var2 varchar(40) );
create table B (
"id" SERIAL PRIMARY KEY,
name varchar(40));
create table C (
"id" SERIAL PRIMARY KEY,
name varchar(40)
, b_id integer references B(id) NOT NULL);
-- Create a trigger function
CREATE OR REPLACE FUNCTION fn_test() RETURNS trigger AS $BODY$
DECLARE
a_id int;
b_id int;
c_id int;
BEGIN
INSERT INTO B (name) VALUES (NEW.var1);
b_id := (select id from B where name = 'NEW.var1');
INSERT INTO C (name, b_id) VALUES (NEW.var2, b_id);
return NEW;
END;
$BODY$ LANGUAGE plpgsql;
-- Create trigger
CREATE TRIGGER tr_test
BEFORE insert or UPDATE
ON A
FOR EACH ROW
EXECUTE PROCEDURE fn_test();
insert into A (var1, var2) values ('Hello', 'World');
ERROR: null value in column "b_id" violates not-null constraint
DETAIL: Failing row contains (1, World, null).
CONTEXT: SQL statement "INSERT INTO C (name, b_id) VALUES (NEW.var2, b_id)"
PL/pgSQL function fn_test() line 17 at SQL statement
re-including the listOn Tue, May 26, 2015 at 9:00 AM, David G. Johnston <david.g.johnston@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.Also, please do not top-post.David J.
<div dir="ltr"><div class="gmail_extra">Marc,</div><div class="gmail_extra"><br /></div><div class="gmail_extra">I have changedthe table C:</div><div class="gmail_extra"><br /></div><div class="gmail_extra"><p class=""><span class="">createtable C ( </span><p class=""><span class=""> "id" SERIAL PRIMARY KEY,</span><pclass=""><span class=""> name varchar(40)</span><p class=""><span class=""> , b_id integer references B(id)DEFERRABLE INITIALLY DEFERRED NOT NULL);</span><p class=""><span class=""><br /></span><p class=""><span class="">Istill get the same error:</span><p class=""><span class="">insert into A (var1, var2) values ('Hello1', 'World1');</span><pclass=""><span class="">ERROR: null value in column "b_id" violates not-null constraint</span><p class=""><spanclass="">DETAIL: Failing row contains (2, World1, null).</span><p class=""><span class="">CONTEXT: SQL statement"INSERT INTO C (name, b_id) VALUES (NEW.var2, b_id)"</span><p class=""><p class=""><span class="">PL/pgSQL functionfn_test() line 17 at SQL statement</span></div></div>
<div dir="ltr"><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br /></div><div class="gmail_extra"><br/><div class="gmail_quote">On Tue, May 26, 2015 at 10:40 AM, Shekar Tippur <span dir="ltr"><<ahref="mailto:ctippur@gmail.com" target="_blank">ctippur@gmail.com</a>></span> wrote:<br /><blockquoteclass="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div dir="ltr"><divclass="gmail_extra">Marc,</div><div class="gmail_extra"><br /></div><div class="gmail_extra">I have changedthe table C:</div><div class="gmail_extra"><br /></div><div class="gmail_extra"><span class=""><p><span>create tableC ( </span><p><span> "id" SERIAL PRIMARY KEY,</span><p><span> name varchar(40)</span></span><p><span> ,b_id integer references B(id) DEFERRABLE INITIALLY DEFERRED NOT NULL);</span><p><span><br/></span><p><span>I still get the same error:</span><p><span>insert into A (var1, var2) values ('Hello1','World1');</span><span class=""><p><span>ERROR: null value in column "b_id" violates not-null constraint</span></span><p><span>DETAIL: Failing row contains (2, World1, null).</span><span class=""><p><span>CONTEXT: SQL statement "INSERT INTO C (name, b_id) VALUES (NEW.var2, b_id)"</span><p><p><span>PL/pgSQLfunction fn_test() line 17 at SQL statement</span></span></div></div></blockquote></div><br/></div><div class="gmail_extra"><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">Becauseyou cannot defer a NOT NULL constraint.</div><div class="gmail_default"style="font-family:arial,helvetica,sans-serif"><br /></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><a href="http://www.postgresql.org/docs/9.4/static/sql-set-constraints.html">http://www.postgresql.org/docs/9.4/static/sql-set-constraints.html</a></div><div class="gmail_default"style="font-family:arial,helvetica,sans-serif"><br /></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">DavidJ.</div><br /></div></div>
I changed the not null constraint but I dont insert the FK id (it is null)
drop table C;
DROP TABLE
s=> create table C (
"id" SERIAL PRIMARY KEY,
name varchar(40)
, b_id integer references B(id)DEFERRABLE INITIALLY DEFERRED);
CREATE TABLE
s=> insert into A (var1, var2) values ('Hello1', 'World1');
INSERT 0 1
s=> select * from C;
1 | World1 |
On Tue, May 26, 2015 at 10:40 AM, Shekar Tippur <ctippur@gmail.com> wrote:Marc,I have changed the table C:create table C (
"id" SERIAL PRIMARY KEY,
name varchar(40)
, b_id integer references B(id) DEFERRABLE INITIALLY DEFERRED NOT NULL);
I still get the same error:
insert into A (var1, var2) values ('Hello1', 'World1');
ERROR: null value in column "b_id" violates not-null constraint
DETAIL: Failing row contains (2, World1, null).
CONTEXT: SQL statement "INSERT INTO C (name, b_id) VALUES (NEW.var2, b_id)"
PL/pgSQL function fn_test() line 17 at SQL statement
Because you cannot defer a NOT NULL constraint.David J.
Hi,
Here is a small snippet on how I got to the error. I am creating a trigger function that returns a trigger.As you can see, I get a error at the end. Appreciate any help in this regard.-- Create table Acreate table A (
var1 varchar(40),
var2 varchar(40) );
-- Create table Bcreate table B (
"id" SERIAL PRIMARY KEY,
name varchar(40));
-- Create table Ccreate table C (
"id" SERIAL PRIMARY KEY,
name varchar(40)
, b_id integer references B(id) NOT NULL);
-- Create a trigger function
CREATE OR REPLACE FUNCTION fn_test() RETURNS trigger AS $BODY$
DECLARE
a_id int;
b_id int;
c_id int;
BEGIN
INSERT INTO B (name) VALUES (NEW.var1);
b_id := (select id from B where name = 'NEW.var1');
INSERT INTO C (name, b_id) VALUES (NEW.var2, b_id);
return NEW;
END;
$BODY$ LANGUAGE plpgsql;
-- Create trigger
CREATE TRIGGER tr_test
BEFORE insert or UPDATE
ON A
FOR EACH ROW
EXECUTE PROCEDURE fn_test();
insert into A (var1, var2) values ('Hello', 'World');
ERROR: null value in column "b_id" violates not-null constraint
DETAIL: Failing row contains (1, World, null).
CONTEXT: SQL statement "INSERT INTO C (name, b_id) VALUES (NEW.var2, b_id)"
PL/pgSQL function fn_test() line 17 at SQL statement
On Tue, May 26, 2015 at 9:14 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:re-including the listOn Tue, May 26, 2015 at 9:00 AM, David G. Johnston <david.g.johnston@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.Also, please do not top-post.David J.