1

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), (... ^

1 Answer 1

3

Since a Python tuple is adapted by Psycopg to a Postgresql anonymous record it is necessary to specify the data types:

from unnest(%s) s(con numeric, id integer)
Sign up to request clarification or add additional context in comments.

1 Comment

Aaaargh! I had tried adding the data types, but I accidentally put them before the variable...

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.