Get the name of the source row table when querying the parent inherited from

Question

I have a Postgres database with several tables that inherit from each other. I can SELECT from the parent table to get the results from all its children, but I need to get the name of the table from which each result comes from.

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

Answer 1

To determine the table in which a particular row is located, use tableoid , for example, you are already there.
The cast to regclass gets the actual name, automatically assigns a schema if necessary with the current search_path .

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

More details:

• Find out which schema is based on table values
• Select (get) all records from multiple schemas using Postgres

Answer 2

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

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