Postgresql subtract comma separated string in one column from another column-CodePudding

The format is like:

col1	col2
V1,V2,V3,V4,V5,V6	V4,V1,V6
V1,V2,V3	V2,V3

I want to create another column called col3 which contains the subtraction of two columns.

What I have tried:

UPDATE myTable
SET col3=(replace(col1,col2,''))

It works well for rows like row2 since the order of replacing patterns matters.

I was wondering if there's a perfect way to achieve the same goal for rows like row1.

So the desired output would be:

col1	col2	col3
V1,V2,V3,V4,V5,V6	V4,V1,V6	V2,V3,V5
V1,V2,V3	V2,V3	V1

Any suggestions would be appreciated!

CodePudding user response：

Split values into tables, subtract sets and then assemble it back. Everything is possible as an expression defining new query column.

with t (col1,col2) as (values
('V1,V2,V3,V4,V5,V6','V4,V1,V6'),
('V1,V2,V3','V2,V3')
)
select col1,col2
     , (
         select string_agg(v,',')
         from (
           select v from unnest(string_to_array(t.col1,',')) as a1(v)
           except
           select v from unnest(string_to_array(t.col2,',')) as a2(v)
         ) x
        )
from t

DB fiddle

CodePudding user response：

You will have to unnest the elements then apply an EXCEPT clause on the "unnested" rows and aggregate back:

select col1, 
       col2, 
       (select string_agg(item,',' order by item)
        from (
           select *
           from string_to_table(col1, ',') as c1(item)
           except 
           select *
           from string_to_table(col2, ',') as c2(item)
        ) t)
from the_table;

I wouldn't store that result in a separate column, but if you really need to introduce even more problems by storing another comma separated list.

update the_table
  set col3 =  (select string_agg(item,',' order by item)
               from (
                  select *
                  from string_to_table(col1, ',') as c1(item)
                  except 
                  select *
                  from string_to_table(col2, ',') as c2(item)
               ) t)
;

string_to_table() requires Postgres 14 or newer. If you are using an older version, you need to use unnest(string_to_array(col1, ',')) instead

If you need that a lot, consider creating a function:

create function remove_items(p_one text, p_other text)
  returns text
as
$$
  select string_agg(item,',' order by item)
  from (
     select *
     from string_to_table(col1, ',') as c1(item)
     except 
     select *
     from string_to_table(col2, ',') as c2(item)
  ) t;
$$
language sql
immutable;

Then the above can be simplified to:

select col1, col2, remove_items(col1, col2)
from the_table;

CodePudding user response：

Note, POSTGRESQL is not my forte, but thought I'd have a go at it. Try:

SELECT col1, col2, RTRIM(REGEXP_REPLACE(Col1,CONCAT('\m(?:', REPLACE(Col2,',','|'),')\M,?'),'','g'), ',') as col3 FROM myTable

See an online fidle.

The idea is to use a regular expession to replace all values, based on following pattern:

\m - Word-boundary at start of word;
(?:V4|V1|V6) - A non-capture group that holds the alternatives from col2;
\M - Word-boundary at end of word;
,? - Optional comma.

When replaced with nothing we need to clean up a possible trailing comma with RTRIM(). See an online demo where I had to replace the word-boundaries with the \b word-boundary to showcase the outcome.