Thread: [SQL] Unable to use INSERT ... RETURNING with column from other table

[SQL] Unable to use INSERT ... RETURNING with column from other table

From
Andreas Joseph Krogh
Date:
Hi.
 
I'm having this schema:
 
create table foo(   id integer PRIMARY KEY,   name varchar not null
);

insert into foo(id, name) values(1, 'one'), (2, 'two');

insert into foo(id, name) select 3, f.name from foo f where f.id = 1 returning id, f.id;

ERROR:  missing FROM-clause entry for table "f" 
LINE 1: ...lect 3, f.name from foo f where f.id = 1 returning id, f.id;

I'd like to return f.id and the inserted id, is this possible?
 
I want this because I'm hoping to use this in a CTE where other selects from this CTE will use this extra column.
 
Thanks.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963

Re: [SQL] Unable to use INSERT ... RETURNING with column from other table

From
Peter Geoghegan
Date:
On Tue, Oct 24, 2017 at 3:04 PM, Andreas Joseph Krogh
<andreas@visena.com> wrote:
> insert into foo(id, name) values(1, 'one'), (2, 'two');
>
> insert into foo(id, name) select 3, f.name from foo f where f.id = 1 returning id, f.id;
>
> ERROR:  missing FROM-clause entry for table "f"
> LINE 1: ...lect 3, f.name from foo f where f.id = 1 returning id, f.id;
>
> I'd like to return f.id and the inserted id, is this possible?

It's possible on 9.5+. You need to assign the target table an alias
using AS -- AS in not a noise word for INSERT (the grammar requires
it).

See the INSERT documentation.

-- 
Peter Geoghegan


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Unable to use INSERT ... RETURNING with column fromother table

From
Andreas Joseph Krogh
Date:
På onsdag 25. oktober 2017 kl. 00:06:59, skrev Peter Geoghegan <pg@bowt.ie>:
On Tue, Oct 24, 2017 at 3:04 PM, Andreas Joseph Krogh
<andreas@visena.com> wrote:
> insert into foo(id, name) values(1, 'one'), (2, 'two');
>
> insert into foo(id, name) select 3, f.name from foo f where f.id = 1 returning id, f.id;
>
> ERROR:  missing FROM-clause entry for table "f"
> LINE 1: ...lect 3, f.name from foo f where f.id = 1 returning id, f.id;
>
> I'd like to return f.id and the inserted id, is this possible?

It's possible on 9.5+. You need to assign the target table an alias
using AS -- AS in not a noise word for INSERT (the grammar requires
it).

See the INSERT documentation.
 
I'm not sure how an alias for the target_table will help me here as I'm trying to return a value not being inserted?
f.id is not inserted, only columns matching f.id.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 

Re: [SQL] Unable to use INSERT ... RETURNING with column fromother table

From
Andreas Joseph Krogh
Date:
På onsdag 25. oktober 2017 kl. 00:49:05, skrev Andreas Joseph Krogh <andreas@visena.com>:
På onsdag 25. oktober 2017 kl. 00:06:59, skrev Peter Geoghegan <pg@bowt.ie>:
On Tue, Oct 24, 2017 at 3:04 PM, Andreas Joseph Krogh
<andreas@visena.com> wrote:
> insert into foo(id, name) values(1, 'one'), (2, 'two');
>
> insert into foo(id, name) select 3, f.name from foo f where f.id = 1 returning id, f.id;
>
> ERROR:  missing FROM-clause entry for table "f"
> LINE 1: ...lect 3, f.name from foo f where f.id = 1 returning id, f.id;
>
> I'd like to return f.id and the inserted id, is this possible?

It's possible on 9.5+. You need to assign the target table an alias
using AS -- AS in not a noise word for INSERT (the grammar requires
it).

See the INSERT documentation.
 
I'm not sure how an alias for the target_table will help me here as I'm trying to return a value not being inserted?
f.id is not inserted, only columns matching f.id.
 
 
 
What I want to accomplish is returning a value from INSERT which is part of the SELECT-expression's FROM-clause, not part of the actual inserted columns:
 
My real-world use-case isn't quite this simple but this sample-case illustrates the problem;
 
DROP TABLE IF EXISTS tbl_value;
DROP TABLE IF EXISTS tbl_header;

CREATE TABLE tbl_header(   id INTEGER PRIMARY KEY,   name VARCHAR NOT NULL
);

CREATE TABLE tbl_value(   id SERIAL PRIMARY KEY,   header_id INTEGER NOT NULL REFERENCES tbl_header(id),   name VARCHAR NOT NULL
);

INSERT INTO tbl_header(id, name) VALUES(1, 'header_one'), (2, 'header_two');

INSERT INTO tbl_value(id, header_id, name)
VALUES(1, 1, 'value 1'),(2, 1, 'value 2'),(3, 1, 'value 3')   , (4, 2, 'value 1'),(5, 2, 'value 2'),(6, 2, 'value 3');

SELECT setval('tbl_value_id_seq', 6);

WITH upd_h(new_header_id, header_name, old_header_id) AS (   INSERT INTO tbl_header(id, name)       SELECT 3, h.name       FROM tbl_header h WHERE h.id = 1
    RETURNING id, name, 1 -- need h.id here
)   INSERT INTO tbl_value(header_id, name)
SELECT f.new_header_id, hv.name
FROM tbl_value hv   JOIN tbl_header h ON hv.header_id = h.id   JOIN upd_h AS f ON hv.header_id = f.old_header_id
;

select h.*, v.* from tbl_header h JOIN tbl_value v ON v.header_id = h.id ORDER BY h.id, v.id;
 
idnameidheader_idname
1header_one11value 1
1header_one21value 2
1header_one31value 3
2header_two42value 1
2header_two52value 2
2header_two62value 3
3header_one73value 1
3header_one83value 2
3header_one93value 3
 
 
 
I need to return the value for h.id in the first INSERT:
 
WITH upd_h(new_header_id, header_name, old_header_id) AS (   INSERT INTO tbl_header(id, name)       SELECT 3, h.name       FROM tbl_header h WHERE h.id = 1
    RETURNING id, name, h.id
)   INSERT INTO tbl_value(header_id, name)
SELECT f.new_header_id, hv.name
FROM tbl_value hv   JOIN tbl_header h ON hv.header_id = h.id   JOIN upd_h AS f ON hv.header_id = f.old_header_id
;
 
This fails with:
ERROR:  missing FROM-clause entry for table "h"
LINE 5:     RETURNING id, name, h.id


 
Is what I'm trying to do possible? I'd like to avoid having to use temp-tables and/or PLPgSQL for this as I need to insert many such values in large batches...
 
Thanks.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 

Re: [SQL] Unable to use INSERT ... RETURNING with column from other table

From
"David G. Johnston"
Date:
On Tuesday, October 24, 2017, Andreas Joseph Krogh <andreas@visena.com> wrote:
WITH upd_h(new_header_id, header_name, old_header_id) AS (   INSERT INTO tbl_header(id, name)       SELECT 3, h.name       FROM tbl_header h WHERE h.id = 1
    RETURNING id, name, 1 -- need h.id here
)   INSERT INTO tbl_value(header_id, name)
SELECT f.new_header_id, hv.name
FROM tbl_value hv   JOIN tbl_header h ON hv.header_id = h.id   JOIN upd_h AS f ON hv.header_id = f.old_header_id
;


This fails with:
ERROR:  missing FROM-clause entry for table "h"
LINE 5:     RETURNING id, name, h.id


 
Is what I'm trying to do possible? I'd like to avoid having to use temp-tables and/or PLPgSQL for this as I need to insert many such values in large batches...
 

Not directly that I know of.  Options:

1. Updatable view.

2. In this particular example you can place the desired h.id in its own CTE.  Move the insert into a CTE.  Then join the insert-returning CTE with the input CTE.

WITH input AS ( 1 as hid ),
ins1 as ( insert select where h.id = (select hid from input) returning *)
Select * from ins1 cross join input

David J.

Re: [SQL] Unable to use INSERT ... RETURNING with column from other table

From
Peter Geoghegan
Date:
On Tue, Oct 24, 2017 at 3:49 PM, Andreas Joseph Krogh
<andreas@visena.com> wrote:
> I'm not sure how an alias for the target_table will help me here as I'm trying to return a value not being inserted?
> f.id is not inserted, only columns matching f.id.

I must have had my wires crossed.

As David says, you could use multiple CTEs for this.

-- 
Peter Geoghegan


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Unable to use INSERT ... RETURNING with column from other table

From
Tom Lane
Date:
Peter Geoghegan <pg@bowt.ie> writes:
> As David says, you could use multiple CTEs for this.

Yeah.  The SELECT portion of the query, so far as the outer INSERT
is concerned, is just a black box that yields some column values to
be inserted.  We could wish that the INSERT's RETURNING clause
could examine additional column values that are available inside that
subquery, but I'm afraid that there are insurmountable semantic problems.
In particular, DISTINCT seems to break that entirely --- consider

insert into foo(id, name) select distinct 3, f.name from foo f where ...
returning id, f.id;

We can't just add "f.id" to the set of columns returned by the SELECT
part without changing the semantics of the DISTINCT.  Or if we ignore
that (acting like it was DISTINCT ON (3, f.name)) then we get an
underdetermined value of f.id, which doesn't seem appetizing either.
        regards, tom lane


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Unable to use INSERT ... RETURNING with column fromother table

From
Andreas Joseph Krogh
Date:
På onsdag 25. oktober 2017 kl. 03:56:02, skrev David G. Johnston <david.g.johnston@gmail.com>:
[snip]
Not directly that I know of.  Options:
 
1. Updatable view.
 
2. In this particular example you can place the desired h.id in its own CTE.  Move the insert into a CTE.  Then join the insert-returning CTE with the input CTE.
 
WITH input AS ( 1 as hid ),
ins1 as ( insert select where h.id = (select hid from input) returning *)
Select * from ins1 cross join input
 
This gives too many duplicate rows in my use-case (at first, I think I solved it at the end of this post).
 
Let me give a more detailed complete example:
I want to copy the set of headers with corresponding values for an email-message to a new email-message. In this example I have message 1000 and 1001, and want to copy headers (with list of values) for message 1001 to message 1002.
 
DROP TABLE IF EXISTS tbl_value;
DROP TABLE IF EXISTS tbl_header;
DROP TABLE IF EXISTS tbl_message;

CREATE TABLE tbl_message(   id INTEGER PRIMARY KEY,   name VARCHAR NOT NULL
);

CREATE TABLE tbl_header(   id SERIAL PRIMARY KEY,   message_id INTEGER NOT NULL REFERENCES tbl_message(id),   name VARCHAR NOT NULL,   index INTEGER NOT NULL,   UNIQUE(message_id, name),   UNIQUE(message_id, index)
);

CREATE TABLE tbl_value(   id SERIAL PRIMARY KEY,   header_id INTEGER NOT NULL REFERENCES tbl_header(id),   name VARCHAR NOT NULL,   index INTEGER NOT NULL,   UNIQUE(header_id, name),   UNIQUE(header_id, index)
);

INSERT INTO tbl_message(id, name) VALUES(1000, 'Message'), (1001, 'Message 2'), (1002, 'Message 3');

INSERT INTO tbl_header(id, message_id, name, index) VALUES(1, 1000, 'header_one msg1', 0), (2, 1000, 'header_two msg1', 1);
INSERT INTO tbl_header(id, message_id, name, index) VALUES(3, 1001, 'header_one msg2', 0), (4, 1001, 'header_two msg2', 1);

INSERT INTO tbl_value(id, header_id, name, index)
VALUES(1, 1, 'msg1 h1 value 1', 0),(2, 1, 'msg1 h1 value 2', 1),(3, 1, 'msg1 h1 value 3', 2)   , (4, 2, 'msg1 h2 value 1', 0),(5, 2, 'msg1 h2 value 2', 1),(6, 2, 'msg1 h2 value 3', 2)   , (7, 3, 'msg2 h1 value 1', 0),(8, 3, 'msg2 h1 value 2', 1),(9, 3, 'msg2 h1 value 3', 2)   , (10, 4, 'msg2 h2 value 1', 0),(11, 4, 'msg2 h2 value 2', 1),(12, 4, 'msg2 h2 value 3', 2)
;

SELECT setval('tbl_header_id_seq', 4);
SELECT setval('tbl_value_id_seq', 12);
 
-- headers and values for message 1000
SELECT h.message_id, v.header_id, h.name, h.index, v.id AS value_id, v.name, v.index
FROM tbl_header h
    JOIN tbl_value v ON h.id = v.header_id
WHERE h.message_id = 1000
ORDER BY h.index ASC, v.index ASC;
 
message_idheader_idnameindexvalue_idnameindex
10001header_one msg101msg1 h1 value 10
10001header_one msg102msg1 h1 value 21
10001header_one msg103msg1 h1 value 32
10002header_two msg114msg1 h2 value 10
10002header_two msg115msg1 h2 value 21
10002header_two msg116msg1 h2 value 32

 
-- headers and values for message 1001
SELECT h.message_id, v.header_id, h.name, h.index, v.id AS value_id, v.name, v.index
FROM tbl_header h
    JOIN tbl_value v ON h.id = v.header_id
WHERE h.message_id = 1001
ORDER BY h.index ASC, v.index ASC;
 
message_idheader_idnameindexvalue_idnameindex
10013header_one msg207msg2 h1 value 10
10013header_one msg208msg2 h1 value 21
10013header_one msg209msg2 h1 value 32
10014header_two msg2110msg2 h2 value 10
10014header_two msg2111msg2 h2 value 21
10014header_two msg2112msg2 h2 value 32
 
 
 
Now, try to copy all headers, with values, for message 1001 to message 1002;
 
WITH input AS (   SELECT h.id, h.name, h.index   FROM tbl_header h WHERE h.message_id = 1001
)   , ins1 AS (   INSERT INTO tbl_header(id, message_id, name, index)       SELECT nextval('tbl_header_id_seq'), 1002, h.name, h.index       FROM input h
    RETURNING id
)
INSERT INTO tbl_value(header_id, name, index)
SELECT f.id AS new_header_id, hv.name, hv.index
FROM tbl_value hv   JOIN input h ON hv.header_id = h.id
CROSS JOIN ins1 AS f;
 
This fails with:
ERROR:  duplicate key value violates unique constraint "tbl_value_header_id_index_key"
DETAIL:  Key (header_id, index)=(5, 0) already exists.


 
The reason is that the CROSS JOIN with ins1 gives too many duplicate rows:
 
(has plain SELECT instead of INSERT from the CTEs)
WITH input AS (   SELECT h.id, h.name, h.index   FROM tbl_header h WHERE h.message_id = 1001
)   , ins1 AS (   INSERT INTO tbl_header(id, message_id, name, index)       SELECT nextval('tbl_header_id_seq'), 1002, h.name, h.index       FROM input h
    RETURNING id
)
SELECT f.id AS new_header_id, hv.id, hv.name, hv.index
FROM tbl_value hv   JOIN input h ON hv.header_id = h.id   CROSS JOIN ins1 AS f;
 
new_header_ididnameindex
57msg2 h1 value 10
58msg2 h1 value 21
59msg2 h1 value 32
510msg2 h2 value 10
511msg2 h2 value 21
512msg2 h2 value 32
67msg2 h1 value 10
68msg2 h1 value 21
69msg2 h1 value 32
610msg2 h2 value 10
611msg2 h2 value 21
612msg2 h2 value 32
 
 
Instead, I want this:
 
new_header_ididnameindex
57msg2 h1 value 10
58msg2 h1 value 21
59msg2 h1 value 32
610msg2 h2 value 10
611msg2 h2 value 21
612msg2 h2 value 32
 
 
I figured that if I also JOIN'ed on tbl_header.index then it works:
 
WITH input AS (   SELECT h.id, h.name, h.index   FROM tbl_header h WHERE h.message_id = 1001
)   , ins1 AS (   INSERT INTO tbl_header(id, message_id, name, index)       SELECT nextval('tbl_header_id_seq'), 1002, h.name, h.index       FROM input h
    RETURNING id, index
)
SELECT f.id AS new_header_id, hv.id, hv.name, hv.index
FROM tbl_value hv   JOIN input h ON hv.header_id = h.id   JOIN ins1 AS f ON f.index = h.index;
 
new_header_ididnameindex
57msg2 h1 value 10
58msg2 h1 value 21
59msg2 h1 value 32
610msg2 h2 value 10
611msg2 h2 value 21
612msg2 h2 value 32
 
 
Which then results in this query for also producing the header-values:
 
WITH input AS (   SELECT h.id, h.name, h.index   FROM tbl_header h WHERE h.message_id = 1001
)   , ins1 AS (   INSERT INTO tbl_header(id, message_id, name, index)       SELECT nextval('tbl_header_id_seq'), 1002, h.name, h.index       FROM input h
    RETURNING id, index
)
INSERT INTO tbl_value(header_id, name, index)
SELECT f.id AS new_header_id, hv.name, hv.index
FROM tbl_value hv   JOIN input h ON hv.header_id = h.id   JOIN ins1 AS f ON f.index = h.index;
 
This seems to produce correct results:
 
-- headers and values for message 1002
SELECT h.message_id, v.header_id, h.name, v.id AS value_id, v.name
FROM tbl_header h
    JOIN tbl_value v ON h.id = v.header_id
WHERE h.message_id = 1002;
 
message_idheader_idnamevalue_idname
10025header_one msg213msg2 h1 value 1
10025header_one msg214msg2 h1 value 2
10025header_one msg215msg2 h1 value 3
10026header_two msg216msg2 h2 value 1
10026header_two msg217msg2 h2 value 2
10026header_two msg218msg2 h2 value 3
 
Do anybody see anything potentially wrong with this, or ways to simplify it?
 
Thanks.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 

Re: [SQL] Unable to use INSERT ... RETURNING with column fromother table

From
Andreas Joseph Krogh
Date:
På onsdag 25. oktober 2017 kl. 05:04:44, skrev Tom Lane <tgl@sss.pgh.pa.us>:
Peter Geoghegan <pg@bowt.ie> writes:
> As David says, you could use multiple CTEs for this.

Yeah.  The SELECT portion of the query, so far as the outer INSERT
is concerned, is just a black box that yields some column values to
be inserted.  We could wish that the INSERT's RETURNING clause
could examine additional column values that are available inside that
subquery, but I'm afraid that there are insurmountable semantic problems.
In particular, DISTINCT seems to break that entirely --- consider

insert into foo(id, name)
  select distinct 3, f.name
  from foo f
  where ...
returning id, f.id;

We can't just add "f.id" to the set of columns returned by the SELECT
part without changing the semantics of the DISTINCT.  Or if we ignore
that (acting like it was DISTINCT ON (3, f.name)) then we get an
underdetermined value of f.id, which doesn't seem appetizing either.

regards, tom lane
 
Thanks for the detailed explaination.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963