PostgreSQL update element in array of custom type

Question

I have created simple custom type:

CREATE TYPE skill_progress AS
   (id uuid,
    count smallint);

There is column skills in my table users of type skill_progress[]. I need to update count of element in array skill_progress[] by specified element id. I need something like this:

UPDATE users SET skills['5a0574cc-66b7-4c89-9950-03a2eea0c701'].count = 10
WHERE nick = 'nick4123';

I have created function that does what i need but i would like to find out if there is an easier way.

CREATE OR REPLACE FUNCTION update_sp
(user_id uuid, skill_id uuid, count smallint) RETURNS integer AS $$
DECLARE
    i integer;
    up integer;
    arr skill_progress[];
BEGIN
    SELECT skills INTO arr FROM users WHERE id = user_id;
    SELECT array_upper(arr, 1) INTO up;
    FOR i IN 1..up 
    LOOP
        IF arr[i].id = skill_id THEN
            UPDATE users SET skills[i].count = count WHERE id = user_id;
            RETURN 0;
        END IF;
    END LOOP;
    RETURN 1;
END
$$
LANGUAGE plpgsql;

I think if i create a new table, then it will be too much data duplication. For each user that table should contain few records where user id is repeated. Like this:

row_num  |  user_id  | skill_id  |  count
- - - - - - - - - - - - - - - - - - - - - 
   1     |   1000    |   100     |    2
   1     |   1000    |   101     |    3
   1     |   1000    |   102     |    5
              . . .

What then is the purpose of custom types?

Answer 1

You can update the table without a function:

update users u
set skills[rn].count = 10
from (
    select skill, rn
    from (
        select skill, row_number() over () rn
        from (
            select unnest(skills) skill
            from users
            where nick = 'nick4123'
            ) sub
        ) sub
    where (skill).id = '5a0574cc-66b7-4c89-9950-03a2eea0c701'
    ) sub
where u.nick = 'nick4123'

However, it is really hard to understand, why you have to make your life so much heavier. Ids should be integers (or bigints), user_skills should be a table with user_id referencing users.

---

Update.

Your attempt is quite natural for a programmer accustomed to handling complex internal data structures. A slightly different logic applies to the world of databases, where speed of access to data is of paramount importance, sometimes at the expense of redundant use of storage space.

Your tables might look like this:

create table users (
    user_id int primary key, 
    nick text);

create table skills (
    skill_id int primary key, 
    user_id int references users, 
    skill_count int);

It is a normalized data model. The relation between the tables is known as one-to-many. This pattern is commonly used in millions of applications working with RDBMS. This is not a good place for a detailed discussion of this issue, especially since you can find hundreds of good texts on data modeling and normalization in internet.