Обсуждение: Postgres CTE issues

<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> 

<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> 

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 A

create table A (

var1 varchar(40),

var2 varchar(40) );

-- Create table B

create table B (

"id" SERIAL PRIMARY KEY,

 name varchar(40));

-- Create table C

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

On Tue, May 26, 2015 at 9:14 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:

re-including the list

On Tue, May 26, 2015 at 9:09 AM, Shekar Tippur <ctippur@gmail.com> wrote:

​

On Tue, May 26, 2015 at 9:00 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:

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.

Also, please do not top-post.

David J.

​