I have a database table with nearly 1 million records.
I have added a new column, called concentration.
I then have a function which calculates 'concentration' for each record.
Now, I want to update the records in batch, so I have been looking at the following questions/answers: https://stackoverflow.com/a/33258295/596841, https://stackoverflow.com/a/23324727/596841 and https://stackoverflow.com/a/39626473/596841, but I am not sure how to do this using unnest...
This is my Python 3 function to do the updates:
def BatchUpdateWithConcentration(tablename, concentrations):
connection = psycopg2.connect(dbname=database_name, host=host, port=port, user=username, password=password);
cursor = connection.cursor();
sql = """
update #tablename# as t
set
t.concentration = s.con
FROM unnest(%s) s(con, id)
WHERE t.id = s.id;
"""
cursor.execute(sql.replace('#tablename#',tablename.lower()), (concentrations,))
connection.commit()
cursor.close()
connection.close()
concentrations is an array of tuples:
[(3.718244705238561e-16, 108264), (...)]
The first value is a double precision and the second is an integer, representing the concentration and rowid, respectively.
The error I'm getting is:
psycopg2.ProgrammingError: a column definition list is required for functions returning "record" LINE 5: FROM unnest(ARRAY[(3.718244705238561e-16, 108264), (... ^
