Select current table name from postgresql-CodePudding

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;

CodePudding user response：

You can try the below -

select * from
(
SELECT 'humo_txns', pan_hash, amount FROM humo_txns
UNION ALL
SELECT 'uzcard_txns', pan_hash, amount FROM uzcard_txns
)A
LIMIT 10;

CodePudding user response：

There is no built-in function to do this, you have to type the name in manually in each select

SELECT CAST('humo_txns' AS VARCHAR(256)) AS table_name, pan_hash, amount FROM humo_txns

UNION ALL

SELECT CAST('uzcard_txns' AS VARCHAR(256)) AS table_name, pan_hash, amount FROM uzcard_txns

I recommend to cast at least in the first query to a varchar field which is long enough to hold any table name.

CodePudding user response：

Yes, simply select text literals and use some column alias for this:

SELECT 'humo_txns' AS table_name, pan_hash, amount FROM humo_txns
UNION ALL
SELECT 'uzcard_txns' AS table_name, pan_hash, amount FROM uzcard_txns
LIMIT 10;

(This gives you ten arbitrarily chosen rows from all the rows in the tables, just as in your query. Thus you may get rows from both tables or rows from only one table.)

CodePudding user response：

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;