Concatenate string instead of just replacing it

Question

I have a table with standard columns where I want to perform regular INSERTs.

But one of the columns is of type varchar with special semantics. It's a string that's supposed to behave as a set of strings, where the elements of the set are separated by commas.

Eg. if one row has in that varchar column the value fish,sheep,dove, and I insert the string ,fish,eagle, I want the result to be fish,sheep,dove,eagle (ie. eagle gets added to the set, but fish doesn't because it's already in the set).

I have here this Postgres code that does the "set concatenation" that I want:

SELECT string_agg(unnest, ',') AS x FROM (SELECT DISTINCT unnest(string_to_array('fish,sheep,dove' || ',fish,eagle', ','))) AS x;

But I can't figure out how to apply this logic to insertions.

What I want is something like:

CREATE TABLE IF NOT EXISTS t00(
  userid int8 PRIMARY KEY,
  a      int8,
  b      varchar);

INSERT  INTO t00 (userid,a,b)  VALUES (0,1,'fish,sheep,dove');

INSERT  INTO t00 (userid,a,b)  VALUES (0,1,',fish,eagle')
  ON CONFLICT (userid)
  DO UPDATE SET
    a = EXCLUDED.a,
    b = SELECT string_agg(unnest, ',') AS x FROM (SELECT DISTINCT unnest(string_to_array(t00.b || EXCLUDED.b, ','))) AS x;

How can I achieve something like that?

Answer 1

Storing comma separated values is a huge mistake to begin with. But if you really want to make your life harder than it needs to be, you might want to create a function that merges two comma separated lists:

create function merge_lists(p_one text, p_two text)
  returns text
as
$$
  select string_agg(item, ',')
  from (
    select e.item
    from unnest(string_to_array(p_one, ',')) as e(item)
    where e.item <> '' --< necessary because of the leading , in your data
    union 
    select t.item
    from unnest(string_to_array(p_two, ',')) t(item)
    where t.item <> ''
  ) t;
$$
language sql;  

If you are using Postgres 14 or later, unnest(string_to_array(..., ',')) can be replace with string_to_table(..., ',')

Then your INSERT statement gets a bit simpler:

INSERT  INTO t00 (userid,a,b)  VALUES (0,1,',fish,eagle')
  ON CONFLICT (userid)
  DO UPDATE SET
    a = EXCLUDED.a,
    b = merge_lists(excluded.b, t00.b);