如何在postgresql中更新逗号分隔字符串的值

How can I update/replace a value on a comma separated string column?

如何更新/替换逗号分隔的字符串列上的值?

i.e:

 121720 |    121716 | false,true,34,1,1,true,1,true
 118220 |    118191 | false,true,731,11,11,true,11,true
 142125 |    142037 | false,true,34,28,28,true,28,true
 182105 |    182012 | false,true,34,3,3,true,3,true,,
 185268 |    185191 | false,true,34,2,2,true,2,true,,

How to replace just the second value on the strings (true) for (false)? What if I want to replace it in a different "position" like sixth or eighth?

如何替换字符串的第二个值(true)为(false)?如果我想在第六或第八个不同的“位置”替换它会怎么样?

I have been able to use the split_part function on SELECT but not on UPDATE.

我已经能够在SELECT上使用split_part函数,但不能在UPDATE上使用。

2 个解决方案

#1

As mentioned in the comments above, you should give serious thought to changing your table design. Here is a query which replaces the second CSV value from true to false:

如上面的评论中所述,您应该认真考虑更改表格设计。这是一个查询,它将第二个CSV值从true替换为false:

update your_table
set col = regexp_replace(col, '(.*?),true,(.*)', '\1,false,\2')

The problem with this query, if it works, is that you would possibly need another regex for the other columns. And if you ever add or remove CSV columns all the regex you wrote might break or become invalid.

此查询的问题(如果有效)是您可能需要其他列的其他正则表达式。如果您添加或删除CSV列,您编写的所有正则表达式可能会中断或变为无效。

#2

do you really need regexpres?

你真的需要regexpres吗?

how about creating a function like this?

如何创建这样的功能?

CREATE OR REPLACE FUNCTION csvupdate(csv text, target_upd text)
  RETURNS text AS
$BODY$
declare 
a  text[];
r record;
begin
a :=  string_to_array($1,',');

FOR r IN select  (string_to_array(zz,':')) [1] ::integer id , (string_to_array(zz,':'))[2] ::text  val  from (   select unnest(string_to_array($2,',')) zz  )q1
loop
a[r.id] := r.val;
end loop;

return array_to_string(a,',');
end;
$BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;

select csvupdate('true,false,1,3,4,false','1:false,4:33')

#1