agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedPostgres CTE issues
9+ messages / 4 participants
[nested] [flat]
* Postgres CTE issues
@ 2015-05-26 15:45 Shekar Tippur <ctippur@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Shekar Tippur @ 2015-05-26 15:45 UTC (permalink / raw)
To: pgsql-sql
Hello,
I am new to postgres. I have a scenario where I need a trigger that inserts
values into n tables but dome of those tables are related.
I dug up some documents and I stumbled across CTE or teh with statement.
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.
- Shekar
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgres CTE issues
@ 2015-05-26 16:00 David G. Johnston <david.g.johnston@gmail.com>
parent: Shekar Tippur <ctippur@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2015-05-26 16:00 UTC (permalink / raw)
To: Shekar Tippur <ctippur@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Fwd: Postgres CTE issues
@ 2015-05-26 16:14 David G. Johnston <david.g.johnston@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2015-05-26 16:14 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgres CTE issues
@ 2015-05-26 17:00 Marc Mamin <M.Mamin@intershop.de>
parent: Shekar Tippur <ctippur@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Marc Mamin @ 2015-05-26 17:00 UTC (permalink / raw)
To: Shekar Tippur <ctippur@gmail.com>; pgsql-sql
> 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.
Hello,
Defining your FK as deferrable initially deferred should help here.
regards,
Marc Mamin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgres CTE issues
@ 2015-05-26 17:07 Shekar Tippur <ctippur@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Shekar Tippur @ 2015-05-26 17:07 UTC (permalink / raw)
To: pgsql-sql
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.
>
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgres CTE issues
@ 2015-05-26 17:40 Shekar Tippur <ctippur@gmail.com>
parent: Marc Mamin <M.Mamin@intershop.de>
0 siblings, 1 reply; 9+ messages in thread
From: Shekar Tippur @ 2015-05-26 17:40 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgres CTE issues
@ 2015-05-26 17:56 David G. Johnston <david.g.johnston@gmail.com>
parent: Shekar Tippur <ctippur@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2015-05-26 17:56 UTC (permalink / raw)
To: Shekar Tippur <ctippur@gmail.com>; +Cc: pgsql-sql
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.
http://www.postgresql.org/docs/9.4/static/sql-set-constraints.html
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgres CTE issues
@ 2015-05-26 17:58 Shekar Tippur <ctippur@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Shekar Tippur @ 2015-05-26 17:58 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
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:56 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
>
>
> 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.
>
> http://www.postgresql.org/docs/9.4/static/sql-set-constraints.html
>
> David J.
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgres CTE issues
@ 2015-05-27 06:24 daku.sandor@gmail.com
parent: Shekar Tippur <ctippur@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: daku.sandor@gmail.com @ 2015-05-27 06:24 UTC (permalink / raw)
To: Shekar Tippur <ctippur@gmail.com>; +Cc: pgsql-sql
Hi,
Remove the the single quotes around NEW.var1 and you'll be golden.
Regards,
Sandor Daku
> On 26 May 2015, at 19:07, Shekar Tippur <ctippur@gmail.com> wrote:
>
> 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.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2015-05-27 06:24 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-05-26 15:45 Postgres CTE issues Shekar Tippur <ctippur@gmail.com>
2015-05-26 16:00 ` David G. Johnston <david.g.johnston@gmail.com>
2015-05-26 16:14 ` David G. Johnston <david.g.johnston@gmail.com>
2015-05-26 17:07 ` Shekar Tippur <ctippur@gmail.com>
2015-05-27 06:24 ` daku.sandor@gmail.com
2015-05-26 17:00 ` Marc Mamin <M.Mamin@intershop.de>
2015-05-26 17:40 ` Shekar Tippur <ctippur@gmail.com>
2015-05-26 17:56 ` David G. Johnston <david.g.johnston@gmail.com>
2015-05-26 17:58 ` Shekar Tippur <ctippur@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox