Get child table names in Postgres query

Question

I have a base table

CREATE TABLE base_table (
  id SERIAL PRIMARY KEY
);

And two child tables that inherit from it

CREATE TABLE child_a (
  description TEXT
) INHERITS (base_table);

CREATE TABLE child_b (
  age INT
) INHERITS (base_table);

I'm trying to write a query that will return all of the fields from the base table and all of its children but also append the name of each row's 'parent' table to the results.

So far, I can get all the other fields I need with

SELECT id, description, age
FROM ONLY base_table
NATURAL FULL JOIN child_a
NATURAL FULL JOIN child_b

I'm a little stumped on how to include the parent table name in the results. I'd like the results to be something like

Id, Description,      Age,   TableName
---------------------------------------
1   TestDescription   null   child_a
2   null              10     child_b
3   OtherDescription  null   child_a

Any help would be appreciated!

Isn’t that a duplicate of https://stackoverflow.com/a/24581769/1222135 ? — greg, Oct 23 '17 at 07:02
Almost a duplicate, there is a subtle difference however. Thanks @greg. — klin, Oct 23 '17 at 09:19

klin · Accepted Answer · 2017-10-23T09:17:17.793

Use subqueries instead of child tables:

SELECT id, description, age, coalesce(t1, t2) as tablename
FROM base_table
NATURAL FULL JOIN (
    SELECT *, 'child_a'::text AS t1
    FROM child_a
    ) a
NATURAL FULL JOIN (
    SELECT *, 'child_b'::text AS t2
    FROM child_b
    ) b;

 id |   description    | age | tablename 
----+------------------+-----+-----------
  1 | TestDescription  |     | child_a
  2 |                  |  10 | child_b
  3 | OtherDescription |     | child_a
(3 rows)

Update. There is a nice solution described in ths answer. To use it in this case you have to explicitly specify a base_table for a system column tableoid:

SELECT *, base_table.tableoid::regclass::text AS table_name
FROM base_table
FULL JOIN child_a USING(id)
FULL JOIN child_b USING(id);

Get child table names in Postgres query

1 Answers1