postgresql data type error when updating from value list with null value

Question

When bulk updating data from value list using execute statement with parameter tuples, a null value in the value list is being considered as data type varchar, therefor causing issue when inserting into a non-varchar column, e.g., a double precision. An example would be:

create table mytable (mykey varchar, myvalue double precision);
cur.execute("""update mytable t set (myvalue)=(v.myvalue) from (values %s, %s) v (mykey, myvalue) where t.mykey = v.mykey""", ([('key1', None),('key2', None)]))

the error from this query is:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.ProgrammingError: column "myvalue" is of type double precision but expression is of type text
LINE 1: update mytable t set (myvalue)=(v.myvalue) from (values ('ke...
                                    ^
HINT:  You will need to rewrite or cast the expression.

How can I specify the data type for null value in the value list?

Answer 1

Either you cast it at the set clause as in @Gordon answer or give it a data type hint on the first value tuple:

query = '''
    update mytable t
    set myvalue = v.myvalue
    from (values ('impossible key', 1.0), %s, %s) v (mykey, myvalue)
    where t.mykey = v.mykey
'''
data = [('key1', None),('key2', None)]

cursor.execute(query, data)

Answer 2

The instructions are pretty clear. You need to cast the value:

. . .
set myvalue = (v.myvalue::double precision)

The problem is that null is typeless, but a column has to have a type. Postgres chooses the most general type, and you need an explicit conversion back to a number.