2

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?

Asherlc
  • 1,111
  • 2
  • 12
  • 28

3 Answers3

3

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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
2

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.

Abelisto
  • 14,826
  • 2
  • 33
  • 41
0

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

GregD
  • 2,797
  • 3
  • 28
  • 39