將 hstore 條目添加到未初始化 (NULL) 列

我最近被這個“功能”咬了。

如果您的hstore列未初始化並且您開始向其中添加條目，它們都會被無聲地吞噬而不會出錯。

這是預期的嗎？

create table test_hstore(id int, map hstore);
insert into test_hstore(id,map) values(0, '');
INSERT 0 1

select * from test_hstore ;
 id | map 
----+-----
  0 | 


update test_hstore set map = map || hstore('key1', 'value1') where id = 0;
UPDATE 1

select * from test_hstore;
 id |       map        
----+------------------
  0 | "key1"=>"value1"


update test_hstore set map = null where id = 0;
UPDATE 1

select * from test_hstore;
 id |  map   
----+--------
  0 | (null)


update test_hstore set map = map || hstore('key1', 'value1') where id = 0;
UPDATE 1

select * from test_hstore;
 id |  map   
----+--------
  0 | (null)

如果我不能對列有非空約束，我可以通過做類似的事情來保護自己（這實際上不起作用）：

UPDATE test_hstore SET map = (IF map IS NULL
                               THEN  '' || hstore('key1', 'value1')
                               ELSE map || hstore('key1', 'value1'))
WHERE id = 0;

在 SQL 中，NULL (operator) (value)一般是NULL.

這不是 hstore 獨有的，而是一切的規範。

空字元串''不同於 NULL。'' || 'somestring'是'somestring'，NULL || 'somestring'而是NULL。

對於hstore. 就像NULL + 1是NULL。

如果這對您來說是個問題，您可能應該儲存空hstore值，而不是在列上NULL分配一個約束。NOT NULL

@Craig 提供了詳細的解釋和避免該問題的最佳建議：定義列NOT NULL DEFAULT ''- 將每行 1 個字節添加到儲存（通常）列可以NULL代替的位置。

手頭問題的簡單標準解決方案COALESCE()- 與任何其他可能的數據類型一樣NULL。允許NULL列中的值是完全合理的設計，您只需要正確處理它即可。

你的想法IF很接近，但這不是 SQL 語言的一部分（在標準 SQL 和 Postgres 中都沒有）。其他一些 RDBMS（如 MySQL ）將IF和引入IFNULLSQL，但這些RDBMS 並沒有增加標準功能CASE和COALESCE.

CREATE TEMP TABLE test_hstore AS
SELECT '1'::int AS id, NULL::hstore AS map;  -- test table with 1 row

UPDATE test_hstore SET map = map || hstore('key1', 'value1')
RETURNING *;

編號 | 地圖 
----+--------
1 | （空值）

UPDATE test_hstore SET map = **COALESCE(map, '')** || hstore('key1', 'value1')
RETURNING *;

編號 | 地圖 
----+------------------
1 | “鍵1”=>“值1”

db<>fiddle here

舊sqlfiddle

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