1

I have columns like role1, role2, role3, etc. They are all booleans.

I would like to create a view on this table, that has a roles column of type text[]. If the columns were TRUE, FALSE, TRUE, the view would contain ["role1", "role3"].

Is there any good way to do this that doesn't explode into a ton of CASE WHEN´s? To clarify, I'd be fine with O(n) with CASE WHEN, but not O(2^n) which is what it currently seems to need. :)

7
  • 1
    More often than not, incrementally numbered columns are a symptom of a bad database design and indicate that the table should be split up in a proper one-to-many relationship Feb 23 '20 at 12:13
  • Yes. The columns are not actually named numbers, they're actual role names that I didn't want to disclose.
    – lurf jurv
    Feb 23 '20 at 19:47
  • That still sounds like a de-normalized model Feb 23 '20 at 20:26
  • Yeah I know. I'm on the Heroku free tier for Postgres which limits me to 10,000 rows. I would normally make a "role <-> user" many to many table, but that would skyrocket past 10k rows since, the average user has more than 1 role and there's over 2500 users.
    – lurf jurv
    Feb 26 '20 at 7:56
  • In that case it might be easier to store that in a single text[] column directly Feb 26 '20 at 7:58
3

I suggest a VALUES expression in the lateral subquery.

SELECT t.id, a.roles
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT col
      FROM  (
         VALUES
            ('role1', role1) -- eligible columns
          , ('role2', role2)
          , ('role3', role3)
         ) x(col, val)
      WHERE  val
     )
  ) a(roles);

This pivots (some would say "counter-pivots" or "unpivots") columns to rows, so we can process a set instead of a row.

As a_horse demonstrates, json(b) is versatile enough to also cover this task. And you don't need to spell out eligible columns while all boolean columns are processed. But it seems you have to spell out eligible columns anyway.

Subtle difference: this returns an empty array for "no qualifying values" ({}), while a_horse's query returns NULL for the same.

This should be substantially faster for three reasons:

  1. Only processes eligible columns to begin with. Especially relevant with many additional (possibly big?) columns.
  2. Involves less casting back and forth and the predicate is as cheap as it gets.
  3. ARRAY constructor is faster than array_agg(). See:

Or, simpler yet: plain CASE expressions concatenated with concat_ws(). If a string is good enough:

SELECT t.id
     , concat_ws(','
               , CASE WHEN role1 THEN 'role1' END -- eligible columns
               , CASE WHEN role2 THEN 'role2' END
               , CASE WHEN role3 THEN 'role3' END) AS roles_string
FROM   tbl t;

Should be the fastest possible solution. And not that much more verbose. See:

Or to get the same array as above:

SELECT t.id
     , string_to_array(
          concat_ws(
             ','
           , CASE WHEN role1 THEN 'role1' END -- eligible columns
           , CASE WHEN role2 THEN 'role2' END
           , CASE WHEN role3 THEN 'role3' END)
        , ',') AS roles
FROM   tbl t;

We can also generate the list of eligible columns dynamically from the the system catalogs. (If you don't want to spell out columns after all.)

Or narrow down to eligible columns for the json(b) technique to eliminate possibly expensive noise early. See:

db<>fiddle here (incl. all of the above, a_horse's jsonb query, and a couple of variants)

4
  • @lurf jurv: I would be interested how the query variants actually perform in your case (plus row count and the number & nature of columns). Can you compare with EXPLAIN (ANALYZE)? Feb 25 '20 at 1:02
  • 1
    Thank you so much! This is 21 times faster :) I've done the explain analyze on my actual table. It isn't that big, only 2000 rows, but it's still pretty consistent times. I ran each explain analyze about five times, there was only a few % of variance. pastebin.com/XBgdXUwn
    – lurf jurv
    Feb 26 '20 at 7:41
  • 1
  • 1
    Oh, and one last thing, I also just now tried explain analyze with removing the string_to_array, and it takes it from 1.9ms to 1.1ms. It is only this slow because this is on Heroku's free Postgres tier :) I will keep it with the array though because it makes the code that reads the roles list more reasonable. Thanks again for doing this!
    – lurf jurv
    Feb 26 '20 at 7:52
4

You can convert the row to a JSON value and then use a sub-select:

select t.id, x.roles
from the_table t
  cross join lateral (
     select array_agg(col) as roles
     from jsonb_each_text(to_jsonb(t)) x(col,val)
     where x.val::boolean
       and x.col like 'role%'
  ) x 
1
  • This worked! However, I had to change up the x.col. Otherwise, it failed trying to coerce an earlier column to boolean. So my query looks like "where x.col=any('{foo,bar,baz}') and x.val='true'"
    – lurf jurv
    Feb 24 '20 at 19:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.