將 hstore 鍵動態轉換為一組未知鍵的列

我有一個數據庫，它使用hstore. 為了將它合併到另一個不支持的數據庫中hstore，我想將鍵拆分為額外的列。

使用者可以添加新的自定義欄位，因此我不能提前依賴鍵的知識。答案是“來自 hstore 列的屬性作為視圖中的單獨列？” 不適用於我的問題。

如果一條記錄在其他記錄中沒有鍵，則它應該獲得具有空值的同一列。

我該怎麼做呢？

這也可以非常有效地完成。但是，不在單個語句中，因為 SQL 要求在呼叫時知道返回類型。所以你需要兩個步驟。該解決方案涉及許多先進技術…

假設在他的回答中與@Denver相同的表：

CREATE TABLE hstore_test (
 id serial PRIMARY KEY
, hstore_col hstore
);

解決方案 1：簡單SELECT

在我編寫了下面的交叉表解決方案後，我發現一個簡單的“蠻力”解決方案可能更快。基本上，查詢@Denver 已經發布，動態建構：

步驟 1a：生成查詢

SELECT format(
    'SELECT id, h->%s
     FROM  (SELECT id, hstore_col AS h FROM hstore_test) t;'
   , string_agg(quote_literal(key) || ' AS ' || quote_ident(key), ', h->')
  ) AS sql 
FROM  (
  SELECT DISTINCT key
  FROM   hstore_test, skeys(hstore_col) key
  ORDER  BY 1
  ) sub;

子查詢(SELECT id, hstore_col AS h FROM hstore_test)只是為了獲取h您的列的列別名hstore。

步驟 1b：執行查詢

這會生成以下形式的查詢：

SELECT id, h->'key1' AS key1, h->'key2' AS key2, h->'key3' AS key3
FROM  (SELECT id, hstore_col AS h FROM hstore_test) t;

結果：

id | key1  | key2  | key3
----+-------+-------+-------
 1 | val11 | val12 | val13
 2 | val21 | val22 |
 3 |       |       |        -- for a row where hstore_col IS NULL

解決方案2：crosstab()

對於很多鍵，這可能會表現得更好。可能不是。你必須進行測試。結果與解決方案 1 相同。

tablefunc您需要提供該crosstab()功能的附加擴展。如果您不熟悉，請先閱讀以下內容：

• PostgreSQL 交叉表查詢

步驟 2a：生成查詢

SELECT format(
  $s$SELECT * FROM crosstab(
    $$SELECT h.id, kv.*
      FROM   hstore_test h, each(hstore_col) kv
      ORDER  BY 1, 2$$
  , $$SELECT unnest(%L::text[])$$
  ) AS t(id int, %s text);
  $s$
, array_agg(key)  -- escapes strings automatically
, string_agg(quote_ident(key), ' text, ')  -- needs escaping!
  ) AS sql 
FROM  (
  SELECT DISTINCT key
  FROM   hstore_test, skeys(hstore_col) key
  ORDER  BY 1
  ) sub;

請注意美元報價的嵌套級別。

我在主查詢中使用這種顯式形式而不是CROSS JOIN輔助查詢中的 short 來保留具有空值或 NULLhstore值的行：

LEFT   JOIN LATERAL each(hstore_col) kv ON TRUE

• LATERAL 和 PostgreSQL 中的子查詢有什麼區別？

有關的：

• 使用 CASE 和 GROUP BY 的動態替代方案

步驟 2b：執行查詢

這會生成以下形式的查詢：

SELECT * FROM crosstab(
    $$SELECT h.id, kv.*
      FROM   hstore_test h
      LEFT   JOIN LATERAL each(hstore_col) kv ON TRUE
      ORDER  BY 1, 2$$
  , $$SELECT unnest('{key1,key2,key3}'::text[])$$
  ) AS t(id int, key1 text, key2 text, key3 text);

在第一次執行之前，您可能需要檢查它的合理性。這應該提供優化的性能。

筆記

• 這兩種解決方案都適用於 Postgres 表中最多 1600 列的物理限制的任意數量的鍵。
• 兩者也適用於任何形狀或形式的鍵，最大長度為標識符，預設為 63 字節。
• 除了sm 已經提到的hstore 函式each()之外，我還使用相關函式來辨識鍵。skeys()
• 請務必正確引用列名，以避免可能通過惡意形成的鍵名進行**SQL 注入攻擊。**我用quote_literal()andquote_ident()來處理這個問題。

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