SQL / Postgres - Select column name based on its value

Question

Working with external Postgres DB, I have only read permissions, so I have to deal with a poorly designed tables.

I have a table:

CREATE TABLE table (
    user_id uuid NOT NULL,
    column_a boolean DEFAULT false,
    column_b boolean DEFAULT false,
    column_c boolean DEFAULT false
);

The table is designed in such a way that only one of the 3 columns is set to true (or all of them are false).

I need to select the column name which is set to true. Is there an elegant way to achive this?

user_id	column_a	column_b	column_c
u1	F	F	F
u2	F	T	F

SELECT WHERE user_id = 'u2'

should return 'column_b' in this example (because it is the one that is set to true).

Tried different approaches but can't find an elegant way

Why worry about elegant when you have only 3 columns of interest? — P.Salmon
– P.Salmon, Commented Nov 21, 2022 at 11:04

user330315 · Accepted Answer · 2022-11-21 11:01:46Z

3

You can use a lateral join that turns the columns into rows, then pick the one with a true value:

select t.user_id, f.col
from the_table t
  left join lateral (
    values 
      ('column_a', t.column_a), 
      ('column_b', t.column_b), 
      ('column_c', t.column_c)
  ) as f(col, value) on f.value

answered Nov 21, 2022 at 11:01

user330315

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

1 Comment

szeak Over a year ago

Very nice example.

szeak · Accepted Answer · 2022-11-21 11:01:33Z

0

With CASE .. THEN .. ELSE syntax:

SELECT
  CASE WHEN column_a = true
  THEN column_a
  ELSE CASE WHEN column_b = true
    THEN column_b
    ELSE CASE WHEN column_c = true
      THEN column_c ELSE null
      END
    END
  END as RESULT
FROM table
WHERE user_id = 'u2'

answered Nov 21, 2022 at 11:01

szeak

3351 silver badge8 bronze badges

Collectives™ on Stack Overflow

SQL / Postgres - Select column name based on its value

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related