Postgres: SELECT column name based on Boolean value

Question

I have a table containing Boolean values for travel modes, which looks like:

SELECT * FROM survey;
+----+-------+-------+-------+-------+
| id | bike  |  car  |  bus  | metro |
+----+-------+-------+-------+-------+
| 49 | false | false | true  | false |
| 51 | false | true  | false | false |
| 52 | false | false | false | true  |
| 53 | false | true  | false | false |
+----+-------+-------+-------+-------+

Then I want select only the modes for which the value is true, so that the end result is:

+----+-------+
| id | mode  |
+----+-------+
| 49 | bus   |
| 51 | car   |
| 52 | metro |
| 53 | car   |
+----+-------+

How I achieve this please?

What if more than one column has a true value for a given row? — GMB
– GMB, Commented Oct 28, 2019 at 12:39
I can confirm no two columns have true values for same record as the survey allows user to select one travel mode only. — arilwan
– arilwan, Commented Oct 28, 2019 at 12:40
Looks like a very bad data model then. Make this two tables: one for the travel modes, one for the surveys. The survey table would contain a column referencing the associated travel mode row (e.g. id_travelmode). The query you are looking for would become a mere join of the two tables. — Thorsten Kettner
– Thorsten Kettner, Commented Oct 28, 2019 at 12:51

GMB · Accepted Answer · 2019-10-28 12:43:50Z

2

You can use a case expression:

select
    id,
    case
        when bike  = true then 'bike'
        when car   = true then 'car'
        when bus   = true then 'bus'
        when metro = true then 'metro'
    end mode
from survey

This supposes that for each row, only one column is true. If not, only the value of the first matching column will be returned.

answered Oct 28, 2019 at 12:43

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Comments

Fahmi · Accepted Answer · 2019-10-28 12:43:54Z

1

You can try below way -

select id, mode from
(
select id, 'bike' as mode,bike as val from tablename
union all
select id, 'car',car from tablename
union all
select id, 'bus',bus from tablename
union all
select id, 'metro',metrofrom tablename
)A where val=true

answered Oct 28, 2019 at 12:43

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Comments

user330315 · Accepted Answer · 2019-10-28 12:44:25Z

0

You can use some JSONB magic to avoid listing all columns:

select id, 
       (select k
        from jsonb_each_text(to_jsonb(s) - 'id') as x(k,v)
        where x.v = 'true') as mode
from survey s
order by id;

to_jsonb(s) - 'id' converts the whole row into a JSON value where the column name is the key and removes the id key. jsonb_each_text() then returns all values as text and picks the key where the value is true

answered Oct 28, 2019 at 12:44

user330315

2 Comments

arilwan Over a year ago

Ah, yeah. But I think this would be slow for very large records.

user330315 Over a year ago

@arilwan: I don't, but you have to check for yourself. But this is the price you have to pay for a de-normalized data model

Collectives™ on Stack Overflow

Postgres: SELECT column name based on Boolean value

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related