Adding hstore entries to an uninitialized (NULL) column

Question

I got bitten by this "feature" recently.

If your hstore column is uninitialized and you start adding entries to it, they are all silently swallowed without error.

Is this expected?

 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)

If I cannot have a not null constraint on the column, can I safeguard myself by doing something like that(this doesn't actually work):

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

Answer 1

In SQL, NULL (operator) (value) is generally NULL.

This is not unique to hstore, and is the norm for everything.

The empty string '' is different to NULL. '' || 'somestring' is 'somestring', wheras NULL || 'somestring' is NULL.

The same is true for hstore. Just like NULL + 1 is NULL.

If this is a problem for you, you should probably store empty hstore values instead of NULL and assign a NOT NULL constraint on the column.

Answer 2

@Craig provides a detailed explanation and the best advice to avoid the problem: define the column NOT NULL DEFAULT '' - which adds 1 byte per row to storage (typically) where the columns could be NULL instead.

The simple, standard solution for the problem at hand is COALESCE() - like with any other data type that can be NULL. It's a completely reasonable design to allow NULL values in the column, you just have to handle it properly.

_{Your idea with IF was close but that's not part of the SQL language (neither in standard SQL nor in Postgres). Some other RDBMS like MySQL introduce IF and IFNULL to SQL, but those add nothing over the standard features CASE and 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 *;

id  |  map   
----+--------
1   | (null)

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

id  |   map        
----+------------------
1   | "key1"=>"value1"

db<>fiddle here
Old sqlfiddle