According to the postgres documentation, you add a key to an hstore column as follows:
UPDATE tab SET h = h || ('c' => '3');
But it seems to only work if the hstore field is not empty. For example:
postgres=# create table htest (t text, h hstore);
CREATE TABLE
postgres=# insert into htest (t) VALUES ('key');
INSERT 0 1
postgres=# update htest set h = h || ('foo'=>'bar') where t='key';
UPDATE 1
postgres=# select * from htest;
t | h
-----+---
key |
(1 row)
The update was successful, yet the hstore was not updated. However:
postgres=# update htest set h = ('foo'=>'bar') where t='key';
UPDATE 1
postgres=# select * from htest;
t | h
-----+--------------
key | "foo"=>"bar"
(1 row)
postgres=# update htest set h = h || ('bar'=>'foo') where t='key';
UPDATE 1
postgres=# select * from htest;
t | h
-----+----------------------------
key | "bar"=>"foo", "foo"=>"bar"
(1 row)
Is there a way to atomically add a key to an hstore without first checking if the hstore is empty?
The hstore module is used to implement the hstore data type in the form of key-value pairs for a single value within PostgreSQL. The hstore data type is remarkably effective in many cases, such as, multiple rows with multiple attributes which are rarely queried for or semi-structured data.
This module implements the hstore data type for storing sets of key/value pairs within a single PostgreSQL value. This can be useful in various scenarios, such as rows with many attributes that are rarely examined, or semi-structured data. Keys and values are simply text strings.
hstore is a PostgreSQL extension that implements the hstore data type. It's a key-value data type for PostgreSQL that's been around since before JSON and JSONB data types were added.
I think the problem here is that the hstore you have is null, and null OR some hstore is null.
The best solution I have, which is probably not the best solution, is to make the table with a default empty hstore rather than allowing null. Then your examples work as you would like:
postgres=# create table htest (t text, h hstore default hstore(array[]::varchar[]));
CREATE TABLE
postgres=# insert into htest (t) values ('key');
INSERT 0 1
postgres=# update htest set h = h || ('foo'=>'bar') where t='key';
UPDATE 1
postgres=# select * from htest;
t | h
-----+--------------
key | "foo"=>"bar"
(1 row)
I unfortunately do not see a cleaner way to create an empty hstore than hstore(array[]::varchar[])
but that doesn't mean there isn't a better way. You could incorporate this into your hstore update from before like so:
update htest set h = coalesce(h, hstore(array[]::varchar[])) || ('foo'=>'bar') where t='key';
This way you don't need to recreate the table. I find that fairly gross though. Hope this helps.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With