Asherlc Asherlc - 1 month ago 8
SQL Question

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?

Answer

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 page.id
FROM   pages
RETURNING tableoid::regclass::text, 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: