SQL / Postgres - Select column name based on its value-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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'