Using NULLIF with Psycopg2 type error

Question

I'm trying to use the NULLIF function to filter out some empty entries in an INSERT INTO command via psycopg2.

The problem is it won't work if the column expects a numeric, as the NULLIF function seems to be interpreted as text.

My table contains 5 columns:

cursor.execute(CREATE TABLE myDb.myTable (id serial PRIMARY KEY, name varchar, value_min decimal, value_max decimal, action_name varchar, nb_solutions integer);")

I insert some data from an array of strings using:

cursor.executemany("INSERT INTO myDb.myTable (name, value_min, value_max, action_name, nb_solutions) VALUES (%s, %s, %s, NULLIF(%s,'None'), %s)", myArray[1:len(myArray)])

In this case, the NULLIF is correctly processed and inserts the 4th string %s from myArray, or Null (depending on if the array contains 'None').

But when I try to apply NULLIF to the 5th column, which is integer, NULLIF is suddenly interpreted as text:

cursor.executemany("INSERT INTO myDb.myTable (name, value_min, value_max, action_name, nb_solutions) VALUES (%s, %s, %s, %s, NULLIF(%s,'None'))", myArray[1:len(myArray)])

And I get the following error:

ProgrammingError: column "nb_solutions" is of type integer but expression is of type text LINE 1: ...6085', ' 13.077', ' epsi', NULLIF(' 7... ^ HINT: You will need to rewrite or cast the expression. 
  args = ('column "nb_solutions" is of type integer but exp...You will need to rewrite or cast the expression.\n',) 
  cursor = <cursor object at 0x000000000578F3A8; closed: 0> 
  diag = <psycopg2.extensions.Diagnostics object> 
  pgcode = '42804' 
  pgerror = 'ERROR: column "nb_solutions" is of type integer...You will need to rewrite or cast the expression.\n' 
  with_traceback = <built-in method with_traceback of ProgrammingError object> Exported something to DB Mytable

Anyone know why this is happening?

Clodoaldo Neto Clodoaldo Neto · Accepted Answer · 2017-03-01T12:34:02

Do not pass None as the 'None' string. Pass the real None value and Psycopg will adapt it correctly. And cast it to the intended type:

cursor.executemany('''
    INSERT INTO myDb.myTable (
        name, value_min, value_max, action_name, nb_solutions
    ) VALUES (%s, %s, %s, %s, %s::int)
''', myArray[1:len(myArray)])

Using NULLIF with Psycopg2 type error

1 Answers