I want to set the value of a column based on the table being queried.
Example:
Let’s assume two tables: Transportation and Locations. And a SELECT statement with a column named type which shall hold ‘Transportation’ or ‘Locations’ based on the table being queried. Is there a way to do this? Maybe something similar to a “RegEx lookahead”?
Advertisement
Answer
tableoid is the way to go, like Adam already suggested.
But this is faster and safer:
SELECT CASE t.tableoid WHEN 'transportation'::regclass THEN 'Transportation'
WHEN 'locations'::regclass THEN 'Locations'
ELSE 'Unknown' END AS type
FROM some_table t;
This way we only cast said tables to oid once – instead of two casts for every row. Plus, comparing OIDs (4-byte integer internally) is cheaper than text. Around 10x faster in a quick test on Postgres 12.
Schema-qualify the table names if there can be ambiguity:
SELECT CASE t.tableoid WHEN 'public.transportation'::regclass THEN 'Transportation'
WHEN 'public.locations'::regclass THEN 'Locations' ...
Else you depend on the current search_path of the session:
If you actually use mixed-case names like the spelling in the question suggests, you have to add double-quotes:
SELECT CASE t.tableoid WHEN 'public."Transportation"'::regclass THEN 'Transportation'
WHEN 'public."Locations"'::regclass THEN 'Locations' ...
See:
If said tables might not exist, you risk an error from the cast. You can prevent that using to_regclass() instead:
SELECT CASE t.tableoid WHEN to_regclass('transportation') THEN 'Transportation'
WHEN to_regclass('locations') THEN 'Locations' ...
But the function is more costly than the plain cast. See:
None of this seems actually necessary for your use case. You have to adapt the table name in the FROM clause anyway, you can just do the same for the expression in the SELECT clause.
With inheritance or partitioning it can actually be essential, though. See: