SELECT value depending on underlying table name

Question

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"?

Do you have a draft of the query? Seeing the `FROM` clause would help an awful lot. — Eric Brandt, Apr 29 '20 at 19:30

score 2 · Accepted Answer · answered Apr 30 '20 at 01:41

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:

How does the search_path influence identifier resolution and the "current schema"

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:

Are PostgreSQL column names case-sensitive?

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:

How to check if a table exists in a given schema

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:

Get the name of a row's source table when querying the parent it inherits from

score 1 · Answer 2 · answered Apr 29 '20 at 19:39

Use tableoid, casting to regclass and then to text.

testdb=# create table transportation(c text);
CREATE TABLE
testdb=# create table locations(c text);
CREATE TABLE
testdb=# insert into transportation select 'foo';
INSERT 0 1
testdb=# insert into locations select 'bar';
INSERT 0 1
testdb=# select c, CASE WHEN tableoid::regclass::text='transportation' then 'Transportation' when tableoid::regclass::text='locations' then 'Locations' else 'Unknown' end as "type" from locations /* transportation */;
  c  |   type    
-----+-----------
 bar | Locations
(1 row)

testdb=# select c, CASE WHEN tableoid::regclass::text='transportation' then 'Transportation' when tableoid::regclass::text='locations' then 'Locations' else 'Unknown' end as "type" from /*locations */ transportation ;
  c  |      type      
-----+----------------
 foo | Transportation
(1 row)

More information on tableoid.

score 0 · Answer 3 · answered Apr 29 '20 at 19:36

This is usually handled through left joins, with judicious use of coalesce():

select o.*, coalesce(t.name, l.name) as name
from originaltable o left join
     transportation t
     on t.transportation_id = o.combined_id and o.type = 'T' left join
     locations l
     on l.location_id = o.combined_id and o.type = 'L'

SELECT value depending on underlying table name

3 Answers3