user3924619 user3924619 - 1 month ago 9
SQL Question

how to select columns by row and value in postgres?

I got a table like this, values are all booleans, except for

col1
, these are the rownames (the primary-key):

col1 | col2 | col3 | col4 | col5 ...
--------------------------------
row1 | f | t | t | t
row2 | f | f | f | t
row3 | t | f | t | f
:


And I want a query like this:
select all columns for row3 where value=t
, or, perhaps more precisely:
select all column-names for row3 where value=t
.

In this example the answer should be:

col2
col4


Because I know all column-names I can do it by recursion in the caller, I mean e.g. by calling the postgres-client from bash, recursing over the colums for each row I'm interested in. But is there a solution in postgres-sql?

Answer

That is not really how SQL works. SQL works on rows, not columns.

What this suggests is that your data structure is wrong. If, instead, you stored the values in rows like this:

col1    name      value
row1    'col1'    value
. . .

Then you would just do:

select name
from t
group by name
having count(*) = sum(case when value then 1 else 0 end);

With your structure, you need to do a separate subquery for each column. Something like this:

select 'col2'
from yourtable
having count(*) = sum(case when col2 then 1 else 0 end)
union all
select 'col3'
from yourtable
having count(*) = sum(case when col3 then 1 else 0 end)
union all
. . .
Comments