how to select columns by row and value in postgres?

Question

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?

With SQL you select data. A column name is not data, but merely the name of a column where data can be found in. Doesn't select * from mytable where col1 = 'row3' suffice? If not; why not? What does your table actually contain? It looks queer for an entity to only have boolean attributes. — Thorsten Kettner
– Thorsten Kettner, Commented Nov 4, 2016 at 12:38
@Thorsten: What it actually contains is the ids of distinct controlled vocabularies in col1. row1,row2,... are all such ids. The Names of the columns, starting from col2, are ids of books referenced by the respective vocabulary or not (that's why the values are Boolean). I wanted to use SQL to build a table that answers two kinds of questions: — user3924619
– user3924619, Commented Nov 5, 2016 at 2:16
sorry, continued ...two kind of questions: 1. Which book(s) are referenced in given vocabulary? 2. Which vocabularies is given book referenced in? — user3924619
– user3924619, Commented Nov 5, 2016 at 2:24

Gordon Linoff · Accepted Answer · 2016-11-04 11:36:19Z

3

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

answered Nov 4, 2016 at 11:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Thorsten Kettner · Accepted Answer · 2016-11-05 20:08:34Z

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;

thx a lot for that. For now I'm going with a pure shell-solution -- time is not an issue for the application. But I bookmarked that, for later reference.

Collectives™ on Stack Overflow

how to select columns by row and value in postgres?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related