PostgreSQL array of data composite update element using where condition

Question

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')]

Please provide sample test data, as insertable text values or a fiddle, and the results expected of that data. — Belayer
– Belayer, Commented Aug 13, 2022 at 21:07
Your problems stem from the fact that the data model design is terribly bad. data_list should be a table (id, user_id, value), change it asap and forget arrays of composite type forever. — klin
– klin, Commented Aug 13, 2022 at 21:37
@klin why do you think that I must forget arrays of composite ? Is there any performance or any other problem about it ? because if i use another table for it, i have to store too many things too ... (in real, tbl has 3 primary keys so i have to store those 3 keys with each data too which will be too much data) — HelloMachine
– HelloMachine, Commented Aug 13, 2022 at 21:39
Because it's an antipattern. Because you have no idea how to perform basic data operations like simple updates. And with this model, there is nothing strange about it. By the way, a table cannot have three primary keys. — klin
– klin, Commented Aug 13, 2022 at 21:54

Patrick · Accepted Answer · 2022-08-14 06:33:43Z

1

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, typically 8kB. All behind the scene so it will work, but at a performance penalty. Even though array_replace sounds like changes are made in-place (and they indeed are in memory), the UPDATE command will write a completely new tuple to disk. So if you have 4,000 array elements that means that at least 40kB of data will have to be read (8 bytes for the mydata_t type on a typical system x 4,000 = 32kB in a TOAST file, plus the main page of the table, 8kB) and then written to disk after the update. A real performance killer.

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, especially if you add the appropriate indexes. 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.

edited Aug 14, 2022 at 6:33

answered Aug 13, 2022 at 23:14

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

klin Over a year ago

+1 for the notes about performance. Though I'm afraid the query is not what the OP meant because it assumes the old value is known.

Patrick Over a year ago

@klin Thanks for the up-vote and the hint. Answer expanded.

HelloMachine Over a year ago

@Patrick thx but i have a question .... if i know the value and i use your first method (array_replace) still there is performance problem or not ? for example i have 4000 elements in my array ... using array_replace will be OK or still it's bad idea ? because i think using array_replace will requires no new array creation ... am i right ?

HelloMachine Over a year ago

@klin You please check my top comment (this post) too ... I would like to hear your answer too ... Thank you.

Patrick Over a year ago

See my expanded answer

Collectives™ on Stack Overflow

PostgreSQL array of data composite update element using where condition

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related