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
CodePudding user response:
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.