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

One option here to workaround the problem of not knowing how to articulate a NULL value in R which the PostgresSQL pacakge will be able to successfully translate is to simply not specify the column whose value you want to be NULL in the database.

So in your example you could use this:

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

when you want col1 to have a NULL value. This of course assumes that col1 in your table is nullable, which may not be the case.