Writing NULL into Postgresql database via Shiny app-CodePudding

I have a ShinyApp that lets the user enter several input values which should be then stored in a postgresql database. The user is also able to modify entries which leads to the following problem:

If the user removes an existing textInput and tries to store an empty textInput in the database, the code does not convert the NA to give NULL over to PostgreSQL. It writes "NA" as a character into the database instead. This is my code snippet:

observeEvent(input$ActionButtonContinue, {
   df <- data.frame(
      column1 = ifelse(input$textInput != "", input$textInput, NA))

query <- "UPDATE table SET column1 = $1 WHERE id = 1"
parameters <- list(df$column1)

con <- getDBConnection() #a function that creates a connection to the database
dbExecute(con, query, parameters)
dbDisconnect(con)
})

If my textInput is empty, the cell is empty containing an NA and the textInput otherwise. df$column1 also evaluates as NA with type logical if the textInput is empty. However, the database column1 contains "NA" instead of [null].

Changing the query to query <- UPDATE table SET column1 = NULL WHERE id = 1 works...

Thus my question, how do I pass over an NA such that Postgresql writes a NULL into the database? Since there are multiple inputs, it is not an option to hard code "NULL" into the query.

CodePudding user response：

Try this link: query <- UPDATE table SET column1 = NULLIF($1, 'NA')::text WHERE id = 1 works.

CodePudding user response：

sprintf can be used instead of dbExecute's parameters argument to build the query string:

library(RPostgres)
library(DBI)
library(datasets)

dbConnection <- dbConnect(
  RPostgres::Postgres(),
  bigint="integer",
  dbname = "test",
  host = "localhost",
  port = 5432,
  user = "postgres",
  password = "postgres"
)


dbWriteTable(dbConnection, "iris", iris)
dbExecute(dbConnection, sprintf('UPDATE iris SET "Sepal.Width" = %s WHERE "Species" = \'setosa\';', "NULL"))
# dbReadTable(dbConnection, "iris")