CODEWITHSUNDEEP
sqlpostgresqlsql-returning
Asherlc
Asherlc

Reputation: 1161

Include table name in RETURNING from INSERT statement

I have an INSERT statement like so that returns the inserted IDs:

INSERT INTO encyclopedias (page_id)
SELECT page.id FROM pages
RETURNING id;

INSERT INTO cookbooks (page_id)
SELECT page.id FROM pages
RETURNING id;

Which returns something like:

 id 
----
1
2

 id 
----
3
4

When parsing the output, I'd like tell which table the IDs are from, like either:

encyclopedia id
----
1
2

cookbook id
----
3
4

or:

table, id
----
encyclopedias, 1
encyclopedias, 2

table, id
----
cookbooks, 3
cookbooks, 4

How would I do that?

Upvotes: 2

Views: 239

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657932

There is a simple solution to automate this using the system column tableoid.

That's the internal OID (object ID) of the source table, which can be cast to regclass to convert it to the actual table name.

INSERT INTO encyclopedias (page_id)
SELECT id FROM pages
RETURNING tableoid::regclass::text AS table, page_id AS id;

Returns your desired output exactly:

table         | id
--------------+----
encyclopedias | 1
encyclopedias | 2

If you change the table in the FROM clause (like in your example), you don't need to adapt the RETURNING clause.

Related:

Upvotes: 3

Abelisto
Abelisto

Reputation: 15624

Use column aliases and constants in the returning clause:

insert into t(x) values(1) returning x as xx, 'the table' as table_name;
╔════╤════════════╗
║ xx │ table_name ║
╠════╪════════════╣
║  1 │ the table  ║
╚════╧════════════╝

Upd:

Additionally you can to specify several output format settings for the psql, for example:

$ echo "
> \pset format unaligned
> \pset tuples_only on
> \echo --==## foo ##==--
> select 1,2,3;" | psql
Output format is unaligned.
Tuples only is on.
--==## foo ##==--
1|2|3

Find more in the documentation.

Upvotes: 2

GregD
GregD

Reputation: 2867

One of the ways to do that is by using the WITH queries:

WITH inserts AS (
INSERT INTO encyclopedias (page_id)
SELECT page.id FROM pages
RETURNING id
)
SELECT inserts.id AS encyclopedia_id;

or

WITH inserts AS (
INSERT INTO encyclopedias (page_id)
SELECT page.id FROM pages
RETURNING id
)
SELECT 'encyclopedia' AS "table", inserts.id AS "id";

More information on the PostgreSQL Documentation page about INSERT

Upvotes: 0

Related Questions