PostgreSQL 中的 EAV

我正在建構一個需要多次部署的應用程序，它具有相對組織良好的數據模型。需要為每個部署配置一個表，其中包含少量附加欄位，每次都不同。性能不是問題，因為我們談論的是幾百行和十幾個附加欄位。

我傾向於保留一個獨特的通用數據模型，因此我正在嘗試（可能是天真地）為該表使用類似 EAV 模型的東西：

• user: 表有公共欄位
• user_data: user_id, field_id, field_value
• field: id, field_name, field_type

然後我的意圖是crosstab在 user_data 表中創建一個表，例如 user_id、field1、field2、field3 ……然後我可以加入我的user表。

但是，crosstab對於數據透視的輸出，需要一個自定義類型，所有欄位名稱和類型都將放在列中。我將所有資訊都儲存在field表中。

那麼我的問題是：我可以根據儲存在field表中的資訊在 PostgreSQL 中動態創建自定義類型嗎？

當然不是最優雅的方式，但它適用於動態 sql：

DO $$
DECLARE 
 sql1 text := '''SELECT user_id AS row_name, field_name AS category, field_value AS value 
   FROM user_data a JOIN field b ON a.field_id = b.id''';
 sql2 text := '''SELECT field_name FROM field ORDER BY 1''';
 sql3 text;
 sql4 text;
BEGIN
 SELECT string_agg(v, ', ') FROM (
   SELECT CONCAT('"', field_name, '" ', field_type) AS v FROM field ORDER BY field_name)
 x INTO sql3;
 sql4 := CONCAT('CREATE TEMP TABLE tmp_pdata AS SELECT * FROM crosstab(', 
                sql1, ', ', sql2, ') AS ct(row_name int, ', sql3, ');');
 EXECUTE sql4;
END $$;
SELECT * FROM tmp_data;

所有欄位的輸入速度都相當快，我擁有的數據量很少（少於 10k 行要旋轉，預計不會快速增長）

對 type 的單個文件類型列使用相同的表定義json，jsonb或者hstore為安裝之間不同的少數列儲存額外的鍵/值對。如果需要動態/靈活，這通常是最有效的解決方案。

如果動態列的總數不超過幾十個，您甚至可以為每個安裝實現相同的列超集，並且只用實際值填充正在使用的列。其餘的可以保持 NULL。NULL 儲存在 Postgres 中很便宜，基本上每個欄位 1 位。

有關的：

• 這個鍵值數據庫模式有名稱嗎？
• 大表中的完全空列如何影響性能？

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