Get the name of a row's source table when querying the parent it inherits from

Question

I have a Postgres database with several tables that inherit from one other. I can SELECT from the parent table to get results from all it's children, but need to get the name of the table that each result originates from.

The method found here does not work since I am only querying the one table, and do not know which children will be in the results ahead of time.

Answer 1

To identify the source table of a particular row, use the tableoid, like you found yourself already.
A cast to regclass retrieves the actual name, automatically schema-qualified where needed according to the current search_path.

SELECT *, tableoid::regclass::text AS table_name
FROM   master.tbl
WHERE  <some_condition>;

More:

• Find out which schema based on table values
• Select (retrieve) all records from multiple schemas using Postgres
• How does the search_path influence identifier resolution and the "current schema"

Answer 2

This is answered in the PostgreSQL documentation, in the section on inheritance. One can use the hidden tableoid column of the table along with relname from pg_class to add a column containing the table names to the result, as shown:

SELECT t.*, p.relname FROM table t, pg_class p WHERE t.tableoid = p.oid;