将密钥添加到空的hstore列 [英] Adding a key to an empty hstore column
问题描述
根据postgres文档,将密钥添加到hstore列,如下所示:
According to the postgres documentation, you add a key to an hstore column as follows:
UPDATE tab SET h = h || ('c' => '3');
但这似乎仅在hstore字段不为空的情况下有效。例如:
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)
更新成功,但hstore未更新。但是:
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)
是否可以在不先检查hstore是否为空的情况下自动向hstore添加密钥?
Is there a way to atomically add a key to an hstore without first checking if the hstore is empty?
推荐答案
我认为这里的问题是您拥有的hstore为null,并且null或某些hstore为null。
I think the problem here is that the hstore you have is null, and null OR some hstore is null.
我有最好的解决方案,可能不是最好的解决方案是使表具有默认的空hstore而不是允许为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)
我很遗憾没有与 hstore(array [] :: varchar [])
相比,可以找到一种更干净的创建空hstore的方法,但这并不意味着没有更好的方法。您可以像以前这样将其合并到您的hstore更新中:
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.
这篇关于将密钥添加到空的hstore列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!