Select current table name from postgresql

Question

I have a query that consists of multiple subqueries that are combined by UNION ALL.

SELECT pan_hash, amount FROM humo_txns

UNION ALL

SELECT pan_hash, amount FROM uzcard_txns

LIMIT 10;

But after retrieving the data I need to somehow find out the source of the data, so I thought it would be a good idea to also include the table name, where the data came from, as a new column.

So is there any way to accomplish something like this?

SELECT <table_name>, pan_hash, amount FROM humo_txns

UNION ALL

SELECT <table_name>, pan_hash, amount FROM uzcard_txns

LIMIT 10;

Answer 1

You don't have to hard-code the table names. You can use the special built-in system column tableoid which identifies the table from which a row comes from. As the tableoid is a number, you need to cast it to a regclass to see the actual name:

SELECT tableoid::regclass AS table_name, pan_hash, amount 
FROM humo_txns
UNION ALL
SELECT tableoid::regclass, pan_hash, amount 
FROM uzcard_txns
LIMIT 10;