CODEWITHSUNDEEP
sqlpostgresql
Zhipeng YANG
Zhipeng YANG

Reputation: 807

How to get the column name of a value in PostgreSQL?

Suppose we have 3 tables:

Table1:

ID FrenchCity
1  Paris
2  Lille
3  Lyon

Table2:

ID IntlCity
1  Lille
2  Geneva
3  Toulouse

Table3:

ID BritishCity
1  London
2  Leeds

I would like to get the column name correspondent with a value. For instance, I give a value Lille and SQL should return Table1.FrenchCity Table2.IntlCity. As I said, I would like to get the column name of a value. So Lille exists in 2 tables, I would like SQL to return the {{table name}}.{{column name}}

How to write a query to do that?

Upvotes: 0

Views: 6613

Answers (3)

Abelisto
Abelisto

Reputation: 15614

If you don't wont to use DB metadata then you can to convert table data into the series of (column_name, column_value) pairs using row_to_json and json_each_text functions:

with
  -- Demo data start
  Table1(ID, FrenchCity) as (values
    (1, 'Paris'),
    (2, 'Lille'),
    (3, 'Lyon')),
  Table2(ID, IntlCity) as (values
    (1, 'Lille'),
    (2, 'Geneva'),
    (3, 'Toulouse')),
  -- Demo data end
  my_data as (
    select 'Table1' as tbl, j.*
    from Table1 as t, json_each_text(row_to_json(t.*)) as j(fld,val)
    union all
    select 'Table2' as tbl, j.*
    from Table2 as t, json_each_text(row_to_json(t.*)) as j(fld,val)
    -- ... and so on ...
  )
select *, format('%s.%s', tbl, fld) as desirede_value from my_data
where val ilike 'lille';

  tbl   |    fld     |  val  |  desirede_value   
--------+------------+-------+-------------------
 Table1 | frenchcity | Lille | Table1.frenchcity
 Table2 | intlcity   | Lille | Table2.intlcity
(2 rows)

Upvotes: 0

Vao Tsun
Vao Tsun

Reputation: 51466

you better create one table with 3 columns: 

ID COUNTRY  fieldName  CITY
1  France   FrenchCity Paris
2  France   FrenchCity Lille
3  France   FrenchCity Lyon
4  Intl     IntlCity   Lille
5  Intl     IntlCity   Geneva
6  Intl     IntlCity   Toulouse

ect. then use query:

SELECT country || '.' || fieldName  
FROM three_col_table
WHERE CITY = 'Lille'

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

This work for you ?

 SELECT 'Table1.FrenchCity' as fieldName
 FROM Table1
 WHERE FrenchCity = 'Lille'

 UNION ALL

 SELECT 'Table2.IntlCity' as fieldName
 FROM Table2
 WHERE IntlCity = 'Lille'

 UNION ALL

 SELECT 'Table3.BritishCity' as fieldName
 FROM Table3
 WHERE BritishCity = 'Lille'

Then you can use array_agg

SELECT array_agg(fieldName)
FROM (
       previous union query
     ) t

Upvotes: 2

Related Questions