Question 272131 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql: Find which table a specific record in an inherited table belongs to

Considering the case in which we’ve got two tables, say: SubscriptionPayments and OrderPayments inherit the shared table Payments.

Is there a way to find out within the table Payments which table any record belongs to; in other words, is it possible to indicate whether a record in Payments is a part of SubscriptionPayments without joining?

Edit:

I’ve already thought of joining, casing, and even adding a discriminator column.
There must be some metadata relating inheriting tables to their base, so I thought there might be some metadata that indicates what record belongs to which table, something like a built-in discriminator.

Tags:

2

Answers


  1. 0

    If I understand your question correctly, you can use the discriminator column for this purpose

    Login or Signup to reply.
  2. 0

    Yes, there’s tableoid system column, meant exactly for that:

    tableoid
    The OID of the table containing this row. This column is particularly handy for queries that select from partitioned tables (see Section 5.11) or inheritance hierarchies (see Section 5.10), since without it, it’s difficult to tell which individual table a row came from. The tableoid can be joined against the oid column of pg_class to obtain the table name.

    Demo:

    create table "Payments"(id int);
create table "SubscriptionPayments"() inherits ("Payments");
create table "OrderPayments"() inherits ("Payments");
 
insert into "Payments" select 1;
insert into "SubscriptionPayments" select 2;
insert into "OrderPayments" select 3;
select * from "Payments";

    All records in Payments do appear to be indistinguishable even though all columns were requested:

    id
    1
    2
    3

    That’s because by default, system columns are hidden unless explicitly listed, so you need to add tableoid to the list. It’s a (not very readable) oid, but it can be converted to the actual table name by casting to regclass:

    select tableoid, tableoid::regclass, * from "Payments";
    tableoid tableoid id
    16384 "Payments" 1
    16387 "SubscriptionPayments" 2
    16390 "OrderPayments" 3
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search