從布爾列建構數組

我有 , , 等列role1。role2它們role3都是布爾值。

我想在這個表上創建一個視圖，它的角色列類型為text[]。如果列是TRUE, FALSE, TRUE，則視圖將包含["role1", "role3"].

有什麼好的方法可以做到這一點不會爆炸成噸CASE WHEN嗎？為了澄清，我可以使用 O(n) with CASE WHEN，但不是 O(2^n) ，這是它目前似乎需要的。:)

我建議VALUES在橫向子查詢中使用表達式。

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);

這將列“反透視”為行，因此我們可以處理一組而不是一行。

正如a_horse 所展示的，json( b) 的通用性足以涵蓋這項任務。在處理所有列時，您無需拼出符合條件的列boolean。但似乎無論如何您都必須拼出符合條件的列。

細微的差別：這會為“無限定值”( {}) 返回一個空數組，而 a_horse 的查詢返回NULL相同的結果。

由於三個原因，這應該會更快：

1. 僅處理符合條件的列。特別是與許多其他（可能很大？）列相關。
2. 涉及更少的來迴轉換，並且謂詞盡可能便宜。
3. ARRAY 建構子比array_agg(). 看：

• 為什麼 array_agg() 比非聚合 ARRAY() 建構子慢？

或者，更簡單：用 .CASE連接的普通表達式concat_ws()。如果字元串足夠好：

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;

應該是最快的解決方案。並沒有那麼冗長。看：

• 如何連接 Postgres SELECT 中的列？
• 合併兩列並添加到一個新列中

或者得到與上面相同的數組：

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;

我們還可以從系統目錄中動態生成符合條件的列列表。（如果您畢竟不想拼出列。）

或者縮小到json( b) 技術的合格列，以及早消除可能昂貴的噪音。看：

• Postgres多列到json

db<>fiddle here - 包括。以上所有內容，a_horse 的 jsonb 查詢，以及幾個變體

引用自：https://dba.stackexchange.com/questions/260331