Show child table name when selecting a row from parent table

Question

I have a table with inheritance with 4 child tables.
How can I know the source table of a specific row?

Answer 1

From PostgreSQL documentation on inheritance:

By doing a join with pg_class you can see the actual table names:

SELECT p.relname, c.name, c.altitude
FROM cities c, pg_class p
WHERE c.altitude > 500 AND c.tableoid = p.oid;

In the example above, p.relname would return the specific child table the row was fetched from.

Answer 2

Use the system column tableoid, it's available for any table.
But you don't need to join to pg_class. Just cast to regclass (and then to text, optionally).

SELECT tbl_id, tableoid::regclass::text AS source
FROM   tbl
WHERE  <some condition>;

Related:

• Select (retrieve) all records from multiple schemas using Postgres