0

I have a PostgreSQL table which is defined as follow

        """ 

        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[]
        )
        """

to insert values I use the following function which is totally inspired from http://www.postgresqltutorial.com/postgresql-python/insert/

def insert_similarities(connection, user_id,sim_user,sim,rank):
    """ insert a similarities into user_similarities"""
    sql = """INSERT INTO user_similarities(user_id,similarities)
             VALUES(%s,%s::similarity[]);"""
    #conn = None
    #vendor_id = None
    test = [(sim_user,sim_,rank)]

    try:
        # read database configuration
        # connect to the PostgreSQL database
        conn = connection
        # create a new cursor
        cur = conn.cursor()
        # execute the INSERT statement
        cur.execute(sql, (user_id,test))
        # commit the changes to the database
        conn.commit()
        # close communication with the database
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)

What I want to achieve, is to append a new similarity type value to the array of similarity of a given user. In https://www.postgresql.org/docs/9.1/functions-array.html there are already existing functions that do the job but I am not using them correctly and I don't know how to do it... The function that I want to use is: enter image description here

I tried a lot of sql queries, here is one an example, obviously not successful

sql = """UPDATE user_similarities SET similarities = array_append(similarities, %s::similarity[]) WHERE user_id = %s;"""

to test the output I do the following:

conn = psycopg2.connect("dbname=recommendation_t user=Etienne")
cur = conn.cursor()
cur.execute(sql,([40,0.2,2],2))

And I get:

CannotCoerce: cannot cast type integer to similarity
LINE 1: ...T similarities = array_append(similarities, ARRAY[40,0.2,2]:...
                                                           ^
3
  • 1
    You should probably do array_append(similarities, %s::similarity) instead of array_append(similarities, %s::similarity[]) . Second argument is an element, not array. I didn't try it : ) Commented Nov 10, 2019 at 16:20
  • It worked! thank you! If you want put it as an answer and I will validate it Commented Nov 11, 2019 at 9:39
  • Sure thing! I'm glad it worked. Commented Nov 13, 2019 at 16:39

1 Answer 1

1

Second argument of array_append is an array element, not array. You have to replace

array_append(similarities, %s::similarity[])

with

array_append(similarities, %s::similarity) 
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.