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

Question

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?

You example is very short. Not sure what you want. Please explain more. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Jun 17, 2016 at 15:12
@JuanCarlosOropeza Well, it's a fault. There're 3 tables. XD — Zhipeng YANG
– Zhipeng YANG, Commented Jun 17, 2016 at 15:14

Juan Carlos Oropeza · Accepted Answer · 2016-06-17 15:15:36Z

2

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

answered Jun 17, 2016 at 15:15

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Juan Carlos Oropeza · Accepted Answer · 2016-06-17 15:24:06Z

1

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'

edited Jun 17, 2016 at 15:24

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

answered Jun 17, 2016 at 15:16

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

1 Comment

Zhipeng YANG Over a year ago

This is just a simple example, but in our database, we have hundreds of column.

Abelisto · Accepted Answer · 2016-06-17 18:34:14Z

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)

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related