Parameters and NULL

Question

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

In PostgreSQL:

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

In R:

It works:

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

But this causes 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 error message:

Error: wait string

In RPostgreSQL, the error is:

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

Substitution NA will be fine with me, but this is not a workaround - literally "NA" is written to the database.

Using, for example, integer (0) gives the same message, "Waiting for a line."

Answer 1

- , NULL R, PostgresSQL pacakge , - , NULL .

, :

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

, col1 NULL. , , , col1 NULL, .

Answer 2

. , , . , PostgreSQL, . :

CREATE OR REPLACE FUNCTION add_stuff(ii integer, tt text, bb bytea)
RETURNS integer
AS
$$
DECLARE
  bb_comp bytea;
  rows    integer;
BEGIN
  bb_comp = convert_to('NA', 'UTF8'); -- my database is in UTF8. 
  -- front-end catches ii is NA; RPostgres blows up 
  -- trying to convert 'NA' to integer.
  tt = nullif(tt, 'NA');
  bb = nullif(bb, bb_comp);
  INSERT INTO foo VALUES (ii, tt, bb);
  GET DIAGNOSTICS rows = ROW_COUNT;
  RETURN rows;
END;
$$
LANGUAGE plpgsql VOLATILE;

, RPostgres , NULL/NA . , , , , -.:)

"" , - "NA" - , NULL/NA (, NA = " " ); , . .

Answer 3

NULLIF insert:

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

NA.