Get the Name of a Row's Source Table When Querying the Parent It Inherits From

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

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"

Get the table name of each row from inherited tables

You can use the tableoid hidden column for this.

SELECT tableoid, * FROM users;

or in this case:

SELECT tableoid = 'admins'::regclass AS is_admin, * FROM users;

Note, however, that this will fall apart horribly if you want to find a non-leaf membership, i.e. if there was superusers that inherited from admins, a superuser would be reported here with is_admin false.

AFAIK there's no test for "is member of a relation or any child relation(s)", though if you really had t you could get the oids of all the child relations with a subquery, doing a tableoid IN (SELECT ...).

Get child table names in Postgres query

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);

SELECT value depending on underlying table name

tableoid is the way to go, like Adam already suggested.

But this is faster and safer:

SELECT CASE t.tableoid WHEN 'transportation'::regclass THEN 'Transportation'
                       WHEN 'locations'::regclass      THEN 'Locations'
                       ELSE 'Unknown' END AS type
FROM   some_table t;

This way we only cast said tables to oid once - instead of two casts for every row. Plus, comparing OIDs (4-byte integer internally) is cheaper than text. Around 10x faster in a quick test on Postgres 12.

Schema-qualify the table names if there can be ambiguity:

SELECT CASE t.tableoid WHEN 'public.transportation'::regclass THEN 'Transportation'
                       WHEN 'public.locations'::regclass      THEN 'Locations' ...

Else you depend on the current search_path of the session:

• How does the search_path influence identifier resolution and the "current schema"

If you actually use mixed-case names like the spelling in the question suggests, you have to add double-quotes:

SELECT CASE t.tableoid WHEN 'public."Transportation"'::regclass THEN 'Transportation'
                       WHEN 'public."Locations"'::regclass      THEN 'Locations' ...

See:

• Are PostgreSQL column names case-sensitive?

If said tables might not exist, you risk an error from the cast. You can prevent that using to_regclass() instead:

SELECT CASE t.tableoid WHEN to_regclass('transportation') THEN 'Transportation'
                       WHEN to_regclass('locations')      THEN 'Locations' ...

But the function is more costly than the plain cast. See:

• How to check if a table exists in a given schema

None of this seems actually necessary for your use case. You have to adapt the table name in the FROM clause anyway, you can just do the same for the expression in the SELECT clause.

With inheritance or partitioning it can actually be essential, though. See:

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

Include table name in RETURNING from INSERT statement

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 id FROM pages
RETURNING tableoid::regclass::text AS table, page_id AS 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:

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

get table name in Postgres query result

Use this:

SELECT DISTINCT column_1 AS keywords, 'table_1' AS tablename
FROM table_1
UNION ALL
SELECT DISTINCT column_2 AS keywords, 'table_2' AS tablename
FROM table_2

It adds one more column tablename to result set, which contains originating table name.

SQL property from inherited Parent-Child relationship

use a MSSQL functions. It will drastically improve performance comparing to nested and recursive queries.

e.g. Function:

CREATE FUNCTION getUserList (@inputUser VARCHAR(250), @inputDate VARCHAR(250))
RETURNS @UserList TABLE
   (
    userName   varchar(250),
   )
AS
BEGIN
   INSERT @UserList
        SELECT a.Substitute_User,
        FROM Table2 AS a
        WHERE a.User = @inputUser
        AND a.BeginDate <= @inputDate
        AND a.EndDate >= @inputDate
   RETURN
END

And afterwards use it as a source as substitute user list e.g.

SELECT Permission_On FROM Table1 WHERE
User IN (SELECT userName FROM Table1('usr1','2-2-2011'))
OR User = 'usr1'

--Edit--

Ohh, I see, I misunderstood the user with the Substitute user..
In such case you can use SQL CTE functionality:

Take a look at this
SQLFiddle

In this query you should replace the User and both the dates in the query

WITH AccessHierarchy ( Userr ) AS
(
   -- Base case
   SELECT
      Userr
   FROM Table1
   WHERE Userr = 'usr3'

   UNION ALL

   -- Recursive step
   SELECT
      e.Userr
   FROM Table2 e
      INNER JOIN AccessHierarchy eh ON
         e.Substitute_User = eh.Userr
      WHERE e.Userr != e.Substitute_User
      AND BeginDate <= '2011-05-05'
      AND EndDate >= '2011-05-05'
)

SELECT Permission_On FROM Table1
WHERE Userr IN
(Select Userr
FROM AccessHierarchy
)

And you can create test tables with this statement:

CREATE TABLE Table1 
    (
     Userr VARCHAR(255) primary key, 
     Permission_On varchar(255)
    );

INSERT INTO Table1
(Userr, Permission_On)
VALUES
('usr1', 'folA'),
('usr2', 'folB'),
('usr3', 'folC');



CREATE TABLE Table2
    (
     Userr VARCHAR(255), 
     Substitute_User varchar(255), 
     BeginDate DATETIME, 
     EndDate DATETIME, 
    );

INSERT INTO Table2
(Userr, Substitute_User, BeginDate, EndDate)
VALUES
('usr1', 'usr1', '2010-01-01', '2010-01-01'),
('usr2', 'usr2', '2010-01-01', '2010-01-01'),
('usr1', 'usr2', '2010-01-01', '2012-01-01'),
('usr3', 'usr3', '2010-01-01', '2010-01-01'),
('usr2', 'usr3', '2011-01-01', '2013-01-01');

Query all fields when using single table inheritance

Answering my own question now after asking helped a lot. This is in the documentation to inheritance. There it says:

only the columns that are local to that base class are queried
[…]
the orm.with_polymorphic() function provides a special AliasedClass that represents a range of columns across subclasses.

This means I can simply

rows=session.query(with_polymorphic(Parent, [Child])).all()

in my above example and it will only have to query the database once to print all rows with their child-class attributes.

---

Related Topics