Postgresql – Append elements to array only if the element does’t exist

postgresqlpostgresql-9.3

WITH upd AS (
  UPDATE univ.products
  SET source = source::int[] || _source
  WHERE project_id = _project_id AND product_id = _products AND NOT(_source = ANY(source))
  RETURNING id
)

, ins AS (
  INSERT INTO univ.products(project_id, product_id, source, tracked)
  SELECT _project_id, _product_id, ARRAY[_source], _tracked
  WHERE NOT EXISTS (SELECT 1 FROM upd) 
  RETURNING id
 )

SELECT id
FROM   upd NATURAL FULL OUTER JOIN ins 
INTO   _project_product_id;

My situation, I have this function which contains all the above and more, what I am trying to avoid is duplicating the project/products pair, but in the same time update the sources by appending new sources. Now the sources for each product/project pair need to be unique, thats why I've added NOT(_source = ANY(source)), but if this is true the select 1 from up will not return anything so will try to insert the records into the db to avoid this I've added the below exception.

EXCEPTION WHEN UNIQUE_VIOLATION THEN
    EXIT;
END;

This is almost doing what I want, but is not returning any id is there a better way of doing this?

Also I have noticed that if I insert a record, then I insert the same record with a different source(the row id will be 1), and then I insert a different record the row id will be 3 instead of 2;

[EDIT] I've solved my problem by doing:

SET source = CASE WHEN NOT(source::int[] @> ARRAY[_source]) THEN source::int[] || _source ELSE source END

Is there a better solution? This one seems a bit slow.

Best Answer

It's not super efficient, but you can define something like your solution as a function:

create or replace function set_append(anyarray, anyelement)
  returns anyarray
  immutable
  PARALLEL safe
  language sql as
$$
  select case when $2=any($1) then $1 else $1 || $2 end;
$$;

You can also use that to define an aggregate:

CREATE AGGREGATE set_agg( ANYELEMENT ) (
  SFUNC = set_append,
  STYPE = ANYARRAY,
  INITCOND = '{}');

This will let you select set_agg(x order by y) to control the order of the distinct elements where you can't do select array_agg(distinct x order by y).

Related Question