Selecting subset of columns in table based on their names using SQL

Question

I have a table in Postgres DB which has a lot of columns such as "id, name, a01, a02, a03, ..., a20, b, c, d, e, f". I want to check whether any of these 'aXX' columns has value 'Y'. I know the trivial way which is:

SELECT name FROM table T 
WHERE T.a01 = Y OR T.a02 = Y OR ... OR T.a20 = Y

I was wondering if there is any way to use a loop or nested select query based on these column names since they have a pattern, instead of hard-coding them individually in WHERE?

Thanks in advance.

This is one of many reasons why database normalization is important — Tom H, Dec 21 '17 at 18:28

Emilio Platzer · Accepted Answer · 2017-12-21T20:37:18.590

Imposible in SQL but...

You can do it in PostgreSQL

If you only want the id, the field name (key) and the value you can write:

select d.id, e.key, e.value
  from the_data d, jsonb_each_text(to_jsonb(d.*)) e
  where value::integer = -1;

If you want the row you can:

select * 
  from the_data
  where id in (
    select d.id
      from the_data d, jsonb_each_text(to_jsonb(d.*))
      where value::integer = -1
  );

See the running example in: http://rextester.com/CRGXPS45970

EDITED

You can filter fields or what you want. For example:

select d.id, e.key, e.value
  from the_data d, jsonb_each_text(to_jsonb(d.*)) e
  where value::integer = -1 and key like 'a%';

select * 
  from the_data
  where id in (
    select d.id
      from the_data d, jsonb_each_text(to_jsonb(d.*))
      where value::integer = -1 and key like 'a%'
  );

You can see it here: http://rextester.com/EESKX21438

Thanks you very much! It is very close to the answer I am looking, I want only search in columns which starts with 'a'. ( In your example 'a1', 'a2', 'a3' and 'a10'). I have all 'a1' to 'a20' in my table. Is it possible to filter columns names like that, when we are converting to json? — user1155981, Dec 21 '17 at 20:18

score 0 · Answer 2 · answered Dec 21 '17 at 18:24

0

There's no way to do exactly what you want to do. You could do another query first to fetch all the column names, then process your result in PHP, but I think that is even more complex than just writing columns' names down.

answered Dec 21 '17 at 18:24

ndrwnaguib

5,623
3
28
51

Selecting subset of columns in table based on their names using SQL

2 Answers2

You can do it in PostgreSQL

EDITED