PostgreSQL array of data composite update element using where condition-CodePudding

I have a composite type:

CREATE TYPE mydata_t AS
(
    user_id integer,
    value character(4)
);

Also, I have a table, uses this composite type as an array of mydata_t.

CREATE TABLE tbl
(
    id serial NOT NULL,
    data_list mydata_t[],
    PRIMARY KEY (id)
);

Here I want to update the mydata_t in data_list, where mydata_t.user_id is 100000 But I don't know which array element's user_id is equal to 100000 So I have to make a search first to find the element where its user_id is equal to 100000 ... that's my problem ... I don't know how to make the query .... in fact, I want to update the value of the array element, where it's user_id is equal to 100000 (Also where the id of tbl is for example 1) ... What will be my query?

Something like this (I know it's wrong !!!)

UPDATE "tbl" SET "data_list"[i]."value"='YYYY'
WHERE "id"=1 AND EXISTS (SELECT ROW_NUMBER() OVER() AS i
                         FROM unnest("data_list") "d" WHERE "d"."user_id"=10000 LIMIT 1)

For example, this is my tbl data:

Row1 => id = 1, data = ARRAY[ROW(5,'YYYY'),ROW(6,'YYYY')]

Row2 => id = 2, data = ARRAY[ROW(10,'YYYY'),ROW(11,'YYYY')]

Now i want to update tbl where id is 2 and set the value of one of the tbl.data elements to 'XXXX' where the user_id of element is equal to 11

In fact, the final result of Row2 will be this:

Row2 => id = 2, data = ARRAY[ROW(10,'YYYY'),ROW(11,'XXXX')]

CodePudding user response：

If you know the value value, you can use the array_replace() function to make the change:

UPDATE tbl
SET data_list = array_replace(data_list, (11, 'YYYY')::mydata_t, (11, 'XXXX')::mydata_t)
WHERE id = 2

If you do not know the value value then the situation becomes more complex:

UPDATE tbl SET data_list = data_arr
FROM (
    -- UPDATE doesn't allow aggregate functions so aggregate here
    SELECT array_agg(new_data) AS data_arr
    FROM (
        -- For the id value, get the data_list values that are NOT modified
        SELECT (user_id, value)::mydata_t AS new_data
        FROM tbl, unnest(data_list)
        WHERE id = 2 AND user_id != 11

        UNION

        -- Add the values to update
        VALUES ((11, 'XXXX')::mydata_t) 

         ) x 
     ) y
WHERE id = 2

You should keep in mind, though, that there is an awful lot of work going on in the background that cannot be optimised. The array of mydata_t values has to be examined from start to finish and you cannot use an index on this. Furthermore, updates actually insert a new row in the underlying file on disk and if your array has more than a few entries this will involve substantial work. This gets even more problematic when your arrays are larger than the pagesize of your PostgreSQL server. All behind the scene so it will work, but at a performance penalty.

As @klin pointed out, this design may be more trouble than it is worth. Should you make data_list as table (as I would do), the update query becomes:

UPDATE data_list SET value = 'XXXX'
WHERE id = 2 AND user_id = 11

This will have MUCH better performance. You could then still create a view to publish the data in an aggregated form with a custom type if your business logic so requires.