Parameters and NULL

Question

I'm having trouble passing NULL as an INSERT parameter query using RPostgres and RPostgreSQL:

In PostgreSQL:

create table foo (ival int, tval text, bval bytea);

In R:

This works:

res <- dbSendQuery(con, "INSERT INTO foo VALUES($1, $2, $3)", 
                   params=list(ival=1, 
                               tval= 'not quite null',
                               bval=charToRaw('asdf')
                               )
                    )

But this throws an error:

res <- dbSendQuery(con, "INSERT INTO foo VALUES($1, $2, $3)",
                   params=list(ival=NULL, 
                               tval= 'not quite null',
                               bval=charToRaw('asdf')
                               )
                   )

Using RPostgres, the error message is:

Error: expecting a string

Under RPostgreSQL, the error is:

Error in postgresqlExecStatement(conn, statement, ...) : 
RS-DBI driver: (could not Retrieve the result : ERROR:  invalid input
syntax for integer: "NULL"
)

Substituting NA would be fine with me, but it isn't a work-around - a literal 'NA' gets written to the database.

Using e.g. integer(0) gives the same "expecting a string" message.

Answer 1

You can use NULLIF directly in your insert statement:

res <- dbSendQuery(con, "INSERT INTO foo VALUES(NULLIF($1, 'NULL')::integer, $2, $3)",
                   params=list(ival=NULL, 
                               tval= 'not quite null',
                               bval=charToRaw('asdf')
                               )
                   )

works with NA as well.