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

Question

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.

Answer 1

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