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;
Best Answer
In SQL,
NULL (operator) (value)
is generallyNULL
.This is not unique to hstore, and is the norm for everything.
The empty string
''
is different to NULL.'' || 'somestring'
is'somestring'
, wherasNULL || 'somestring'
isNULL
.The same is true for
hstore
. Just likeNULL + 1
isNULL
.If this is a problem for you, you should probably store empty
hstore
values instead ofNULL
and assign aNOT NULL
constraint on the column.