hstore hacks

Hacks for the hstore datatype.

Determine if an hstore is empty

Problem: you need to determine if an hstore is empty (i.e. does not contain any keys/values, but is not NULL).

Solution: compare against an empty hstore

testdb=# SELECT ''::HSTORE = ''::HSTORE, ''::HSTORE = '{foo=>bar}'::HSTORE;
 ?column? | ?column? 
----------+----------
 t        | f
(1 row)

Example with table data:

testdb=*# INSERT INTO hstore_test VALUES(DEFAULT, 'foo=>bar');
INSERT 0 1
testdb=*# INSERT INTO hstore_test VALUES(DEFAULT, '');
INSERT 0 1
testdb=*# INSERT INTO hstore_test VALUES(DEFAULT, NULL);
INSERT 0 1
testdb=*# SELECT * FROM hstore_test;
 id |     val      
----+--------------
  9 | "foo"=>"bar"
 10 | 
 11 | 
(3 rows)

testdb=*# SELECT * FROM hstore_test WHERE val = ''::HSTORE;
 id | val 
----+-----
 10 | 
(1 row)