CODEWITHSUNDEEP
sqlbashpostgresql
user3924619
user3924619

Reputation: 15

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?

Upvotes: 1

Views: 3164

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

I'm not trying to answer your question here, but want to tell you what database structure would be appropriate for the task described.

  • You have a book table with a book id. Each record contains one book.
  • You have a word table with a word id. Each record contains one word.
  • Now you want to have a list of all existing book-word combinations.

The table you would create for this relation is called a bridge table. One book can contain many words; one word can be contained in many books; a n:m relation. The table has two columns: the book id and the word id. The two combined are the table's primary key (a composite key). Each record contains one existing combination of book and word.

Here are some examples how to use this table:

To find all words contained in a book:

select word
from words
where word_id in
(
  select word_id
  from book_word
  where book_id = 
  (
    select book_id 
    from books 
    where name = 'Peter Pan'
  )
);

(That's just an example; the same can be got with joins instead of subqueries.)

To select words that occur in two particular books:

select word
from words
where word_id in
(
  select word_id
  from book_word
  where book_id in
  (
    select book_id 
    from books 
    where name in ('Peter Pan', 'Treasure Island')
  )
  group by word_id
  having count(*) = 2
);

To find words that occur in only one book:

select w.word, min(b.name) as book_name
from words w 
join book_word bw on bw.word_id = w.word_id
join books b on b.book_id = bw.book_id
group by w.word_id
having count(*) = 1;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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
. . .

Upvotes: 3

Related Questions