Psygopg2 replacing multiple columns with python lists of tuples

Question

I am new to PostgreSQl. I am using Psycopg2 with PostgreSQL 9.5.6. My update SQL looks like this right now. I want to update multiple columns with a Python list of tuples using Psycopg2.

update_sql = '''
            UPDATE my_table
            SET size = n, mean = m, std_pop = std
            FROM unnest(%s) s(my_id integer, n integer, m double precision, std double precision)
            WHERE my_table.id = s.my_id;
        '''

For example, I have the following Python list of tuples (id, new_size, new_mean, new_std) in python:

new_column_values =[(0,10,9.0,8.0),(1,10,9.0,8.0),(2,10,9.0,8.0)]

which I want to put into the table and update all 3 columns at the same time:

+----+------+------+---------+
| id | size | mean | std_pop |
+----+------+------+---------+
|  0 |    0 |    0 |       0 |
|  1 |    0 |    0 |       0 |
|  2 |    0 |    0 |       0 |
+----+------+------+---------+

Would this be best way to update multiple columns? Right now it returns an error:

psycopg2.ProgrammingError: function return row and query-specified return row do not match
DETAIL:  Returned type numeric at ordinal position 3, but query expects double precision.

It's saying it expects double precision, but got numeric type, which I don't know how to solve. My mean and std_pop columns are double precision columns. What's going on here?

@ClodoaldoNeto The python code has a lot of dependencies, but I actually figure out how to solve it. I just replace 'double precision' with 'numeric', but that is counter-intuitive to me because I declared those columns as 'double precision'. — Dobob

Dobob Dobob · Accepted Answer · 2017-03-18T20:38:56

I solved the problem.

I just changed double precision in my update query to numeric and that solved the issue. But it doesn't make sense why I have to use numeric when I declared the column with double precision.

update_sql = '''
        UPDATE my_table
        SET size = n, mean = m, std_pop = std
        FROM unnest(%s) s(my_id integer, n integer, m numeric, std numeric)
        WHERE my_table.id = s.my_id;
    '''

Maybe someone can explain why it has to be numeric and not double precision?

Psygopg2 replacing multiple columns with python lists of tuples

1 Answers