Question 113676 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL / Postgres – Select column name based on its value

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

Tags:

2

Answers


  1. 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'
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search