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

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

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.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

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

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;

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
;

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

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

 

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

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

[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

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;

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

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;

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;

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;

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;

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

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

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

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com