How to remove single occurrence of an element from array in PostgreSQL-CodePudding

I have a table (attributes) as defined

Id, type, value, tokenIds, contract, count, createdAt, updatedAt.

Here: tokenIds is text array and contains comma seperated non-unique ids. i.e: 1,2,3,3,4,4,5.

Its quite straightforward to add more ids in, but in a scenario i have to revert this operation.

Allow me to walk you through a scenario.

NEW = {1, 5, 0x01, headscarf, blue ribbon, {1}, , 1}
NEW = {1, 5, 0x01, headscarf, red ribbon, {2,3}, 2}
NEW = {1, 5, 0x01, headscarf, blue ribbon, {1,1,2}, 2} is REVERTED

REVERT = {1, 5, 0x01, headscarf, blue ribbon, {1}, 1}

I want to delete a list of items from stored array in db in a way that it only deletes only one occurrence of it.

(I tried my best to explain)

I tried ‘array_remove’ it removes all the occurrences, I tried some other methods but they are preventing duplication.

Data set: [1,2,3,3,2,3,4,2,6] If i want to remove (2,3) It remove just one occurrence. So, expected output is: [1,2,3,3,4,2,6]

CodePudding user response：

I think your best bet is to write a function for that:

create function remove_array(p_input text[], p_to_remove text[])
 returns text[]
as 
$$
declare
  l_idx integer;
  l_start text[];
  l_len int;
  l_result text[];
begin
  l_len := cardinality(p_to_remove);
  for l_idx in 1..cardinality(p_input) - l_len   1 loop
    l_start := p_input[l_idx:l_idx   l_len - 1];

    -- found the first matching sub-array
    if l_start = p_to_remove then
    
      -- a match at the start needs to be treated differently
      if l_idx = 1 then 
        l_result := p_input[l_idx   l_len:];
      else 
        l_result := p_input[1:l_idx-1]||p_input[l_idx   l_len:];
      end if;
      
      return l_result;
    end if;
  end loop;
  
  -- array to be removed was not found
  return p_input;
end;
$$
language plpgsql
immutable
strict;

This:

with test_data (input, remove) as (
   values 
     ('{1,2,3,3,2,3,4,2,6}'::text[], '{3,4}'::text[]),
     ('{1,2,3,3,2,3,4,2,6}', '{1,2}'),
     ('{1,2,3,3,2,3,4,2,6}', '{4,2}'),
     ('{1,2,3,3,2,3,4,2,6}', '{4,2,6}'),
     ('{1,2,3,3,2,3,4,2,6}', '{2}'),
     ('{1,2,3,3,2,3,4,2,6}', '{7,8}')
)
select input, remove, remove_array(input, remove) as result
from test_data;

returns:

input               | remove  | result             
-------------------- --------- --------------------
{1,2,3,3,2,3,4,2,6} | {3,4}   | {1,2,3,3,2,2,6}    
{1,2,3,3,2,3,4,2,6} | {1,2}   | {3,3,2,3,4,2,6}    
{1,2,3,3,2,3,4,2,6} | {4,2}   | {1,2,3,3,2,3,6}    
{1,2,3,3,2,3,4,2,6} | {4,2,6} | {1,2,3,3,2,3}      
{1,2,3,3,2,3,4,2,6} | {2}     | {1,3,3,2,3,4,2,6}  
{1,2,3,3,2,3,4,2,6} | {7,8}   | {1,2,3,3,2,3,4,2,6}

There might be a more elegant solution to the problem "find the first position of a an array inside an array", but currently I can't think of one.

CodePudding user response：

A first attempt:

select
string_to_array(
  regexp_replace(
    array_to_string(
     array[1,2,3,3,2,3,4,2,6], ','), 
  '2,3,', ''
   ), 
',')::integer[];

 string_to_array 
-----------------
 {1,3,2,3,4,2,6}