CODEWITHSUNDEEP
sqlpostgresql
Dils Matchanov
Dils Matchanov

Reputation: 577

Select current table name from postgresql

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;

Upvotes: 4

Views: 2606

Answers (4)

Fahmi
Fahmi

Reputation: 37473

You can try the below -

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

Upvotes: 1

Pred
Pred

Reputation: 9042

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(64)) AS table_name, pan_hash, amount FROM humo_txns

UNION ALL

SELECT CAST('uzcard_txns' AS VARCHAR(64)) 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.

Upvotes: 1

user330315
user330315

Reputation:

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;

Upvotes: 10

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

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

Upvotes: 2

Related Questions