PostgreSQL - UPDATE one element of a composite type array

Question

I have a PostgreSQL table which is defined as follows.

    """ 

    CREATE TYPE similarity AS (

    user_id          integer,
    similarity       real,
    rank             integer
    )
    """,
    """
    CREATE TABLE user_similarities (

    user_id      int REFERENCES books_user_id (user_id) ON UPDATE CASCADE ON DELETE CASCADE,
    similarities similarity[]
    )
    """

Here is an example that I use for testing:

What I want to achieve is to update one element of my composite type (similarity) in the array. An example would be to change the similarity to a given value lets say 1, of user_id = 3 and similarities.user_id = 10.

with the following query, I can select user_id = 3 and similarities.similarity = 10, but I don't know how to update my table.

sql = """with list_1 as (SELECT *, unnest(similarities) as list_similarities FROM user_similarities WHERE user_id = 3), id_1 
    as (SELECT * FROM list_1 WHERE(list_1.list_similarities).user_id=10 ) SELECT * FROM id_1"""

I tried the following:

with list_1 as (SELECT *, unnest(similarities) as list_similarities FROM user_similarities WHERE user_id = 3), 
id_1 as (SELECT * FROM list_1 WHERE(list_1.list_similarities).user_id=10 ) UPDATE id_1 SET 
list_similarities.similarity = 1

But I get the error message: relation "id_1" does not exist LINE 2: ...ERE(list_1.list_similarities).user_id=10 ) UPDATE id_1 SET l...

Here is the whole function:

def update_similarity(connection,id_ ):

sql = """with list_1 as (SELECT *, unnest(similarities) as list_similarities FROM user_similarities WHERE user_id = 3), id_1 
as (SELECT * FROM list_1 WHERE(list_1.list_similarities).user_id=10 ) UPDATE id_1 SET list_similarities.similarity = 1 """

to_insert = (id_,)
bol = 0
try:
    conn = connection
    # create a new cursor
    cur = conn.cursor()
    # execute the INSERT statement
    cur.execute(sql, to_insert)
    request = cur.fetchall()
    if len(request) > 0:
        bol = 1
    cur.close()
except (Exception, psycopg2.DatabaseError) as error:
    print(error)

return bol

Any help would be appreciated.

Could you normalize the table and not use an array as a column? This would make it easy to update a single entry. — user12493039
– user12493039, Commented Dec 8, 2019 at 8:36
what do you mean by normalizing it? To have a multiple-row instead of an array? I am considering to do it, but still, I am curious about the solution to my problem. — Babas
– Babas, Commented Dec 8, 2019 at 8:40
@Babas So just to be clear, you want to (for example) update the similarity field in that table's array, where user_id = 3 (or so)? So my question is, do you want to update ALL the objects in that record's similarities array, or only certain ones? If not all, then what's the criteria for determining which elements in the array to update? — 404
– 404, Commented Dec 8, 2019 at 11:59
@404 let's take the table above as an example. One of the rows is user_id = 3 and similarities {(10,0.66,),(7,0.111)}. 10 and 7 correspond to the user_id of composite TYPE similarity and 0.66 and 0.111 correspond to similarity of the composite TYPE similarity (indeed not best naming). What I would like to achieve is to modify the similarity.similarity based on user_id and similarity.user_id. A concrete example is to change (10,0.66,) to (10,0.25,) is it more clear? Hope it is so that you can help me :) — Babas
– Babas, Commented Dec 8, 2019 at 17:26

404 · Accepted Answer · 2019-12-08 23:30:27Z

If I understand correctly, you already know the user_id and the similarity.user_id for which you want to update the similarity.similarity value. I'm assuming you also know the value you want to update it with, and that it's not meant to be calculated as part of this query.

With that in mind this is how you can accomplish it.

Setup, based on your sample data:

CREATE TYPE similarity AS (
    user_id          integer,
    similarity       real,
    rank             integer
);

CREATE TABLE user_similarities (
    id           int,
    user_id      int,
    similarities similarity[]
);

INSERT INTO user_similarities
VALUES
(0, 3, '{"(10,0.66,)","(7,0.111,)"}'),
(1, 2, '{"(3,0.256,)","(7,0.891,)"}');

Query:

UPDATE user_similarities
SET similarities = (
  SELECT ARRAY_AGG(ROW(
    s.user_id,
    CASE s.user_id WHEN 10 THEN 0.25 ELSE s.similarity END,
    s.rank
  )::similarity)
  FROM UNNEST(similarities) s
)
WHERE user_id = 3
AND 10 IN (SELECT (UNNEST(similarities)).user_id);

Result of a SELECT * from that table:

| id  | user_id | similarities                |
| --- | ------- | --------------------------- |
| 0   | 3       | {"(10,0.25,)","(7,0.111,)"} |
| 1   | 2       | {"(3,0.256,)","(7,0.891,)"} |

db-fiddle

Basically, to find the rows to update is easy, you just check that user_id = 3 and then unnest the array and check whether any of the array elements have that user_id value of 10.

Updating the array content is a little less straightforward. What I'm doing there is:

Unnesting the array so I can process each item at a time
Recreating those composite types on the fly (ROW, specifying the 3 fields, casting to your type), but if I find the one(s) with a user_id of 10, I set the similarity value to 0.25, otherwise keep the original value.
Aggregate the composite types back into an array

One thing this does not do (or at least not necessarily) is retain the original order of the elements in the array. If that matters to you, it can be done, but is more of a pain.

Collectives™ on Stack Overflow

PostgreSQL - UPDATE one element of a composite type array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related