PostgreSQL – How to Remove Duplicate Values from Query Result

1st case

You seem to forget the valid_during range. As your third case suggests, there can be multiple entries per (rec_id, val), so you must select the right one:

UPDATE master m
SET    valid_on = f_array_sort(m.valid_on || u.valid_on) -- sorted array, see below
FROM   updates u
WHERE  m.rec_id = u.rec_id 
AND    m.valid_during @> u.valid_on  -- additional check
AND    m.val = u.val 
AND    NOT m.valid_on @> ARRAY[u.valid_on];

I assume the whole possible date range is always covered for each existing rec_id and valid_during shall not overlap per rec_id, or you'd have to do more.

After installing the additional module btree_gist, add an exclusion constraint to rule out overlapping date ranges if you don't have one, yet:

ALTER TABLE master ADD CONSTRAINT EXCLUDE
USING gist (rec_id WITH =, valid_during WITH &&)  -- disallow overlap

The GiST index this is implemented with is also a perfect match for the query. Details:

• Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
• Store the day of the week and time?

2nd / 3rd case

Assuming that every date range starts with the smallest date in the (now sorted!) array: lower(m.valid_during) = m.valid_on[1]. I would enforce that with a CHECK constraint.

Here we need to create one or two new rows In the 2nd case it is enough to shrink the range of the old row and insert one new row In the 3rd case we update the old row with the left half of array and range, insert the new row and finally insert the with the right half of array and range.

Helper functions

To keep it simple I introduce a new constraint: every array is sorted. Use this helper function

-- sort array
CREATE OR REPLACE FUNCTION f_array_sort(anyarray) 
  RETURNS anyarray LANGUAGE sql IMMUTABLE AS
$$SELECT ARRAY (SELECT unnest($1) ORDER BY 1)$$;

I don't need your helper function arraymin() any more, but it could be simplified to:

CREATE OR REPLACE FUNCTION f_array_min(anyarray) 
  RETURNS anyelement LANGUAGE sql IMMUTABLE AS
$$SELECT min(a) FROM unnest($1) a$$;

Two more to get the left and right half of an array split at a given element:

-- split left array at given element
CREATE OR REPLACE FUNCTION f_array_left(anyarray, anyelement) 
  RETURNS anyarray LANGUAGE sql IMMUTABLE AS
$$SELECT ARRAY (SELECT * FROM unnest($1) a WHERE a < $2 ORDER BY 1)$$;

-- split right array at given element
CREATE OR REPLACE FUNCTION f_array_right(anyarray, anyelement) 
  RETURNS anyarray LANGUAGE sql IMMUTABLE AS
$$SELECT ARRAY (SELECT * FROM unnest($1) a WHERE a >= $2 ORDER BY 1)$$;

Query

This does all the rest:

WITH u AS (  -- identify candidates
   SELECT m.id, rec_id, m.val, m.valid_on, m.valid_during
        , u.val AS u_val, u.valid_on AS u_valid_on
   FROM   master  m
   JOIN   updates u USING (rec_id)
   WHERE  m.val <> u.val
   AND    m.valid_during @> u.valid_on
   FOR    UPDATE  -- lock for update
   )
, upd1 AS (  -- case 2: no overlap, no split
   UPDATE master m  -- shrink old row
   SET    valid_during = daterange(lower(u.valid_during), u.u_valid_on)
   FROM   u
   WHERE  u.id = m.id
   AND    u.u_valid_on > m.valid_on[array_upper(m.valid_on, 1)]
   RETURNING m.id
   )
, ins1 AS (  -- insert new row
   INSERT INTO master (rec_id, val, valid_on, valid_during)
   SELECT u.rec_id, u.u_val, ARRAY[u.u_valid_on]
        , daterange(u.u_valid_on, upper(u.valid_during))
   FROM   upd1
   JOIN   u USING (id)
   )
, upd2 AS (  -- case 3: overlap, need to split row
   UPDATE master m  -- shrink to first half
   SET    valid_during = daterange(lower(u.valid_during), u.u_valid_on)
        , valid_on = f_array_left(u.valid_on, u.u_valid_on)
   FROM   u
   LEFT   JOIN upd1 USING (id)
   WHERE  upd1.id IS NULL  -- all others
   AND    u.id = m.id
   RETURNING m.id, f_array_right(u.valid_on, u.u_valid_on) AS arr_right
   )
INSERT INTO master (rec_id, val, valid_on, valid_during)
          -- new row
SELECT u.rec_id, u.u_val, ARRAY[u.u_valid_on]
     , daterange(u.u_valid_on, upd2.arr_right[1])
FROM   upd2
JOIN   u USING (id)
UNION ALL  -- second half of old row
SELECT u.rec_id, u.val, upd2.arr_right
     , daterange(upd2.arr_right[1], upper(u.valid_during))
FROM   upd2
JOIN   u USING (id);

SQL Fiddle.

Notes

• You need to understand the concept of data-modifying CTEs (writeable CTEs), before you touch this. Judging from the code you provided, you know your way around Postgres.

  • Are SELECT type queries the only type that can be nested?

• FOR UPDATE is to avoid race conditions with concurrent write access. If you are the only user writing to the tables, you don't need it.

• I took a piece of paper and drew a timeline so not to get lost in all of this.

• Each row is only updated / inserted once, and operations are simple and roughly optimized. No expensive window functions. This should perform well. Much faster than your previous approach in any case.

• It would be a bit less confusing if you'd use distinct column names for u.valid_on and m.valid_on, which are related but different things.

• I compute the right half of the split array in the RETURNING clause of CTE upd2: f_array_right(u.valid_on, u.u_valid_on) AS arr_right, because I need it several times in the next step. This is a (legal) trick to save one more CTE.

• As for solutions that don't involve unnesting the master table: You have to unnest the array valid_on either way, to split it, at least as long as it's not sorted. Also, your helper function arraymin() is already unnesting it anyway.

Core feature is the window function lag().
Also pay special attention to avoid deadlocks and race conditions with concurrent deletes and inserts (which can affect which rows to delete!):

CREATE OR REPLACE FUNCTION remove_vendor_price_dupes(_vendor int)
  RETURNS integer AS
$func$
DECLARE
   del_ct int;
BEGIN
   -- this may or may not be necessary:
   -- lock rows to avoid race conditions with concurrent deletes
   PERFORM 1
   FROM   vendor_prices
   WHERE  vendor = _vendor
   ORDER  BY sku, effective_date, id  -- guarantee row locks in consistent order
   FOR    UPDATE;

   -- delete redundant prices
   DELETE FROM vendor_prices v
   USING (
      SELECT id
           , price = lag(price) OVER w  -- same as last row
             AND (lead(id) OVER w) IS NOT NULL AS del  -- not last row
      FROM   vendor_prices
      WHERE  vendor = _vendor
      WINDOW w AS (PARTITION BY sku ORDER BY effective_date, id)
      ) d
   WHERE v.id = d.id
   AND   d.del;

   GET DIAGNOSTICS del_ct = ROW_COUNT;  -- optional:
   RETURN del_ct;  -- return number of deleted rows
END
$func$  LANGUAGE plpgsql;

Call:

SELECT remove_vendor_price_dupes(1);

Notes

• The current version of the 9.3 major release is 9.3.6. The project recommends that ...

  all users run the latest available minor release for whatever major version is in use.

• A multicolumn index on (vendor, sku, effective_date, id) would be perfect for this - in this particular order. But Postgres can combine indexes rather efficiently, too.
  It might pay to add the otherwise irrelevant price as last item ot the index to get index-only scans out of this. You'll have to test.

• Since you have concurrent deletes it may be a good idea to run a separate delete per vendor to reduce the potential for race conditions and deadlocks. Since there are only a few vendors, this seems like a reasonable partitioning. (Many tiny calls would be comparatively slow.)

• I am running a separate SELECT (PERFORM in plpgsql, since we do not use the result) because the row locking clause FOR UPDATE cannot be used together with window functions. Don't let the keyword mislead you, this is not just for updates. I am locking all rows for the given vendor, since the result depends on all rows. Concurrent reads are not impaired, only concurrent writes have to wait until we are done. That's another reason why deleting rows for one vendor at a time in a separate transaction should be best.

• sku is unique per product, so we can PARTITION BY it.

• ORDER BY effective_date, id: your first version of the question included code for duplicate rows, so I added id to ORDER BY as additional tie breaker. This way it works for duplicates on (sku, effective_date) as well.

• To preserve the last row for each set: AND (lead(id) OVER w) IS NOT NULL. Reusing the same window for lead() is cheap - independent of the added explicit WINDOW clause - that's just syntax shorthand for convenience.

• I am locking rows in the same order: ORDER BY sku, effective_date, id. Make sure that concurrent DELETEs operate in the same order to avoid deadlocks. If all other transactions delete no more than a single row within the same transaction, there cannot be deadlocks and you don't need the row locking at all.

• If concurrent INSERTs could lead to a different result (make different rows obsolete), you have to lock the whole table in EXCLUSIVE mode instead to avoid race conditions:

  LOCK TABLE vendor_prices IN EXCLUSIVE MODE;
  

  Do that only if it's necessary. It blocks all concurrent write access.

• I am returning the number of rows deleted, but that's totally optional. You might as well return nothing and declare the function as RETURNS void.