postgresql - 使用其他 hstore 值更新 hstore 值

Question

我有一个定期用新数据更新的汇总表。其中一列是 hstore 类型。当我更新新数据时，如果键存在，我想将键的值添加到键的现有值，否则我想将对添加到 hstore。

现有数据:

id     sum     keyvalue
--------------------------------------
1      2       "key1"=>"1","key2"=>"1"

新数据:

id    sum      keyvalue
--------------------------------------------------
1     3        "key1"=>"1","key2"=>"1","key3"=>"1"

想要的结果:

id    sum      keyvalue
--------------------------------------------------
1     5        "key1"=>"2","key2"=>"2","key3"=>"1"

我想在插入的冲突部分执行此操作。求和部分很简单。但是我还没有找到如何以这种方式连接 hstore。

Answer 1

没有内置的int。您必须编写一个函数来接受 hstore 值并以您想要的方式合并它们。

create function merge_and_increment(p_one hstore, p_two hstore)
  returns hstore
as
$$
  select hstore_agg(hstore(k,v))
  from (
    select k, sum(v::int)::text as v
    from (
      select *
      from each(p_one) as t1(k,v)
      union all
      select *
      from each(p_two) as t2(k,v)
    ) x
    group by k
  ) s
$$
language sql;

hstore_agg() 函数也不是内置的，但很容易定义它:

create aggregate hstore_agg(hstore) 
(
  sfunc = hs_concat(hstore, hstore),
  stype = hstore
);

结果是:

select merge_and_increment(hstore('"key1"=>"1","key2"=>"1"'), hstore('"key1"=>"1","key2"=>"1","key3"=>"1"'))

是:

merge_and_increment                  
-------------------------------------
"key1"=>"2", "key2"=>"2", "key3"=>"1"

请注意，如果存在无法转换为整数的值，该函数将严重失败。

对于插入语句，您可以像这样使用它:

insert into the_table (id, sum, data)
values (....) 
on conflict (id) do update
   set sum = the_table.sum + excluded.sum, 
       data = merge_and_increment(the_table.data, excluded.data);