4

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;
Improve this question

4 Answers 4

Reset to default
10

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I think that's what I was looking for. I will check it out little bit later
Not working for me, using SELECT tableoid::regclass AS table_name, ... I'm getting There is a column named "tableoid" in table "custom_field_sections", but it cannot be referenced from this part of the query.
Probably, you have to specify the table as: SELECT custom_field_sections.tableoid::regclass AS table_name, ...
2

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

Improve this answer

Comments

1

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.

Improve this answer

3 Comments

The cast is unnecessary a string constant will automatically have the type text. But if you do want the cast, varchar(64) would be enough for a table name.
@a_horse_with_no_name thanks for this extra info, I am not that familiar with Postgres, in some RDBMS the data type is determined by sampling the data in the first query which could lead to unexpected behavior, hence the recommendation. Changing the size to 64 as you recommended.
There is indeed a function for this (as most of the time in postgres) as the answer from @a_horse_with_no_name states => tableoid::regclass So this answers first sentence is a false claim.
1

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;
Improve this answer

2 Comments

What do you buy by wrapping it that way?
@shawnt00, nothing :) I thought the limit would be for both the select statements

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.