Conditionally delete item inside an Array Field PostgreSQL

Question

I'm building a kind of dictionary app and I have a table for storing words like below:

id | surface_form | examples
-----------------------------------------------------------------------
 1 | sounds       | {"It sounds as though you really do believe that",
   |              |  "A different bell begins to sound midnight"}

Where surface_form is of type CHARACTER VARYING and examples is an array field of CHARACTER VARYING

Since the examples are generated automatically from another API, it might not contain the exact "surface_form". Now I want to keep in examples only sentences that contain the exact surface_form. For instance, in the given example, only the first sentence is kept as it contain sounds, the second should be omitted as it only contain sound.

The problem is I got stuck in how to write a query and/or plSQL stored procedure to update the examples column so that it only has the desired sentences.

Why are you storing that in an array in the first place? This would be a lot easier if your data model was properly normalized — user330315
– user330315, Commented Oct 25, 2016 at 7:42

klin · Accepted Answer · 2016-10-25 07:38:48Z

7

This query skips unwanted array elements:

select id, array_agg(example) new_examples
from a_table, unnest(examples) example
where surface_form = any(string_to_array(example, ' '))
group by id;

 id |                    new_examples                    
----+----------------------------------------------------
  1 | {"It sounds as though you really do believe that"}
(1 row)

Use it in update:

with corrected as (
    select id, array_agg(example) new_examples
    from a_table, unnest(examples) example
    where surface_form = any(string_to_array(example, ' '))
    group by id
)
update a_table
set examples = new_examples
from corrected
where examples <> new_examples
and a_table.id = corrected.id;

Test it in rextester.

edited Oct 25, 2016 at 7:38

answered Oct 25, 2016 at 7:32

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

2ehr · Accepted Answer · 2016-10-25 07:45:26Z

5

Maybe you have to change the table design. This is what PostgreSQL's documentation says about the use of arrays:

Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

Documentation: https://www.postgresql.org/docs/current/static/arrays.html

edited Oct 25, 2016 at 7:45

user330315

answered Oct 25, 2016 at 7:34

2ehr

1391 silver badge7 bronze badges

Comments

user330315 · Accepted Answer · 2016-10-25 08:21:02Z

The most compact solution (but not necessarily the fastest) is to write a function that you pass a regular expression and an array and which then returns a new array that only contains the items matching the regex.

create function get_matching(p_values text[], p_pattern text)
  returns text[]
as
$$
declare
  l_result text[] := '{}'; -- make sure it's not null
  l_element text;
begin
  foreach l_element in array p_values loop

    -- adjust this condition to whatever you want
    if l_element ~ p_pattern then
      l_result := l_result || l_element;
    end if;

  end loop;
  return l_result;
end;
$$
language plpgsql;

The if condition is only an example. You need to adjust that to whatever you exactly store in the surface_form column. Maybe you need to test on word boundaries for the regex or a simple instr() would do - your question is unclear about that.

Cleaning up the table then becomes as simple as:

update the_table
   set examples = get_matching(examples, surface_form);

But the whole approach seems flawed to me. It would be a lot more efficient if you stored the examples in a properly normalized data model.

Chris Travers · Accepted Answer · 2016-10-25 07:23:27Z

1

In SQL, you have to remember two things.

Tuple elements are immutable but rows are mutable via updates.
SQL is declarative, not procedural

So you cannot "conditionally" "delete" a value from an array. You have to think about the question differently. You have to create a new array following a specification. That specification can conditionally include values (using case statements). Then you can overwrite the tuple with the new array.

answered Oct 25, 2016 at 7:23

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Comments

Keith John Hutchison · Accepted Answer · 2016-10-25 07:42:14Z

0

Looks like one way could to update the array with array elements that are valid by doing a select using like or some regular expression.

https://www.postgresql.org/docs/current/static/arrays.html

edited Oct 25, 2016 at 7:42

user330315

answered Oct 25, 2016 at 7:25

Keith John Hutchison

5,34711 gold badges49 silver badges66 bronze badges

Comments

rupat · Accepted Answer · 2016-10-25 07:44:57Z

0

If you want to hold elements from array that have "surface_form" in it you have to use that entries with substring(....,...) is not null

First you unnest the array, hold only items that match, and then array_agg the stored items

Here is a little query you can run to test without any table.

SELECT
  id,
  surface_form,
  (SELECT array_agg(examples_matching)
   FROM unnest(surfaces.examples) AS examples_matching
   WHERE substring(examples_matching, surfaces.surface_form) IS NOT NULL)
FROM
  (SELECT
     1                                              AS id,
     'example' :: TEXT                              AS surface_form,
     ARRAY ['example form', 'test test','second example form'] :: TEXT [] AS examples
  ) surfaces;

edited Oct 25, 2016 at 7:44

user330315

answered Oct 25, 2016 at 7:44

rupat

1861 gold badge1 silver badge6 bronze badges

2 Comments

Laurenz Albe Over a year ago

But that would also find amp --- not sure if that is intended. You might consider adding word boundaries to the pattern.

rupat Over a year ago

you are right. i think the main problem here is to now how to work with arrays in sql. a little adaption he have to do himself ;).

Pooja-G · Accepted Answer · 2016-10-26 06:38:44Z

You can select data in temp table using Then update temp table using update query on row number Merge value using This merge value you can update in original table

For Example

Suppose you create temp table Temp (id int, element character varying) Then update Temp table and nest it. Finally update original table

Here is the query you can directly try to execute in editor

CREATE TEMP TABLE IF NOT EXISTS temp_element (
    id bigint,
    element character varying)WITH (OIDS);
TRUNCATE TABLE temp_element;
insert into temp_element select row_number() over (order by p),p from (
select unnest(ARRAY['It sounds as though you really do believe that',  
'A different bell begins to sound midnight']) as P)t;
update temp_element set element = 'It sounds as though you really' 
where element = 'It sounds as though you really do believe that';
--update table
select array_agg(r) from ( select element from temp_element)r

Collectives™ on Stack Overflow

Conditionally delete item inside an Array Field PostgreSQL

7 Answers 7

Comments

Comments

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related