Search code examples
arrayspostgresqlsql-update

PostgreSQL, how to update an array into an existing array without creating duplicates?


I've a row with a column of text[] which looks like:

{"FName1 LName1", "FName3 LName3", "FName4 LName4"}

Next would I like to merge another array of names into that already stored array, but without creating any duplicates in it. How can I do that?

For example the other array I want to include looks like:

{"FName4 LName4", "FName1 LName1", "FName2 LName2"}

As a result would I like it to be stored like:

{"FName1 LName1", "FName2 LName2", "FName3 LName3", "FName4 LName4"}

The elements within the new array can be in any order, as long as no element (here names) are duplicated within that new (created) stored array.


I couldn't find an PostgreSQL array function which does that for me. I only figured out how to append or prepend an array (with duplicates).

Is there a way without having to read that row first? I really don't want to read the row first.


Edit based on the comment:

Create table including test row example:

CREATE TABLE users (
 indexid TEXT PRIMARY KEY,
 name_list TEXT[] NOT NULL
);
INSERT INTO users VALUES ('iid_123', '{"FName1 LName1", "FName3 LName3", "FName4 LName4"}')

Many thanks to the solution from @a_horse_with_no_name which now works for me like:

UPDATE users 
SET name_list = append_unique(
  name_list, 
  '{"FName4 LName4", "FName1 LName1", "FName2 LName2"}'
) 
WHERE indexid = 'iid_123'

Documentations:

https://www.postgresql.org/docs/current/functions-array.html#ARRAY-FUNCTIONS-TABLE https://www.postgresql.org/docs/current/functions-string.html


Solution

  • You will have to write a function to deal with that:

    create function append_unique(p_input text[], p_to_append text[])
      returns text[]
    as
    $$
       select array_agg(element)
       from (
          select *
          from unnest(p_input) as t(element)
          union 
          select *
          from unnest(p_to_append) as t(element)
       ) x
    $$
    language sql
    immutable;
    

    The UNION will remove the duplicates.