Re: [SQL] Unable to use INSERT ... RETURNING with column fromother table - Mailing list pgsql-sql
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