Conditionally delete an element inside an Array PostgreSQL field

Question

I am creating a kind of application for dictionaries, and I have a table for storing words, as shown 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_formhas type CHARACTER VARYINGand examplesis an array fieldCHARACTER VARYING

Since examples are automatically generated from another API, it may not contain the exact "surface_form". Now I want to contain in the examples only sentences containing the exact form_surface. For example, in this example, only the first sentence is supported, since it contains sounds, the second should be omitted, since it contains only sound.

The problem is that I am stuck in writing a query and / or plSQL stored procedure to update a column examplesso that it has only the right sentences.

Answer 1

The most compact solution (but not necessarily the fastest) is to write a function that passes a regular expression and an array, and then returns a new array containing only the elements matching the regular expression.

create function get_matching(p_values text[], p_pattern text)
  returns text[]
as
$$
declare
  l_result text[] := '{}'; -- make sure it 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 condition ifis just an example. You need to tune this to what you exactly store in the column surface_form. Perhaps you need to check the word boundaries for regular expression or just do it instr()- your question is unclear about this.

Clearing the table becomes as easy as:

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

---

But the whole approach seems wrong to me. It would be much more efficient if you saved the examples in a properly normalized data model.

Answer 2

:

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) 

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; 

rextester.

Answer 3

, . , PostgreSQL :

; . , . , , .

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

Answer 4

SQL .

• Tuple , .
• SQL ,

, "" "" . -. . ( case). .

Answer 5

, , , , .

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

Answer 6

temp, temp, update , ,

, Temp (id int, ) Temp . ,

,

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

Answer 7

" " , (....,...) null

, , , array_agg

Here is a small query that you can run for testing 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;