Update composite type inside array

Question

Is there an easy way to update composite types inside an array?

Currently I have the following table (I truncated other fields):

CREATE TYPE order_item AS (delivery_date DATE, status INT);
CREATE TABLE demo (id SERIAL PRIMARY KEY, data order_item[]);

I want to update the status of all order_items. When it's greater than 1, all status should be updated + 1.

For a table without array field it would be easy:

UPDATE mytab SET complex_col.r = (complex_col).r + 1;

However, I want to do the same inside an array.

score 2 · Accepted Answer · edited May 23 '17 at 12:07

The root of your problem is the relational design. A normalized schema (separate table in 1:n relationship) would be much cleaner than the array column, and easier to index or update etc. Would hardly occupy more space on disk either, array overhead is similar to row overhead.

While stuck with your unfortunate design, you have to unnest the array, update and aggregate back, taking care not to break things at each step:

I want to update the status of all order_items. When it's greater than 1, all status should be updated + 1.

UPDATE demo d
SET    data = x.data
FROM (
   SELECT d.id, array_agg((o.delivery_date
                         , CASE WHEN o.status > 1 THEN o.status + 1 ELSE o.status END
                           )::order_item) AS data
   FROM   demo d
   LEFT   JOIN  LATERAL unnest(data) o ON true
   GROUP  BY d.id
   HAVING count(*) FILTER (WHERE o.status > 1) > 0
   ) x
WHERE  d.id = x.id;

Order of elements is likely not to change, but there are no guarantees without ORDER BY.
To guarantee original order of elements:

PostgreSQL unnest() with element number

actually the table is way way bigger and I only need to update the status caused by a migration. :( — Christian Schmitt, Apr 11 '16 at 20:50
@ChristianSchmitt: Consider the added note about order of elements. — Erwin Brandstetter, Apr 12 '16 at 02:33

Update composite type inside array

1 Answers1