0

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
  • 118,695
  • 26
  • 233
  • 260
sc28
  • 1,163
  • 4
  • 26
  • 48

1 Answers1

1

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)])
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • Thanks, however, not being able to pass the "real" `None` is in fact precisely the reason I need `nullif()`. My array comes from an Ajax post command, during which the data is "stringified". This apparently isn't an issue for integers, who are correctly read by the SQL as numeric, but sometimes among the integers is an undefined value which blocks the SQL command. I thought `nullif()` would help catch these undefined instances and replace them with `Null`, to fix the type. I could clean the data before the SQL, but would prefer `nullif()` directly in the query. Any further thoughts welcome! – sc28 Mar 01 '17 at 14:07
  • So, actually I looked a bit into casting the data to the intended type as you suggested, and by casting the whole `nullif()` expression to `int` (or `decimal`) it unlocked the query. Thanks a lot for having posted that little bit of code, I wouldn't have thought of this trick otherwise! My final VALUES expression is: `(%s, NULLIF(%s,'None')::decimal, NULLIF(%s,'None')::decimal, %s, NULLIF(%s,'None')::int)` – sc28 Mar 01 '17 at 15:44