R language unable to insert in postgresql using postgresqlWriteTable

Question

I am new to R and trying to insert R dataframe in Postgresql. Everytime whenever i try to execute my rscripts.R, I am getting the following error:

"In postgresqlWriteTable(conn, name, value, ...) : table customervalidation exists in database: aborting assignTable"

Table customervalidation already exists in postgresql, I am trying to insert the content of SampleData.csv in this table. All the headers of the csv are already present in the table and they are all in lowercase.

Command line argument

./script.R batch SampleData.csv yes no

rscripts.R content

#!/usr/bin/Rscript

options(echo=TRUE) # if you want see commands in output file
args <- commandArgs(trailingOnly = TRUE)
print(args)
# trailingOnly=TRUE means that only your arguments are returned, check:
# print(commandsArgs(trailingOnly=FALSE))

batchIndicator <- tolower(args[1])
filename <- args[2]
isHeaderPresent <-args[3]
isRunTheBatch<-args[4]
rm(args)
#Library files
library(RPostgreSQL)
#now check whether it is immediate or batch.
# if it is immediate then real time prediction needs to prepare.
# if it is batch then whole batch set needs to prepare and keep the results in a separate file.
if(isHeaderPresent == "yes")
{
  header = TRUE
}else
{
  if(isHeaderPresent == "no"){

    header = FALSE
  }
}

  print(paste("Processing for Batch mode for filename ", filename))
  # Start body for other function
  data <-read.csv(filename,header = header, sep=",")
  drv <- dbDriver("PostgreSQL")
  con <- dbConnect(PostgreSQL(), dbname = "customervalidation", host = "localhost", port =5432 , user = "user", password = "pwd")
  dbWriteTable(con,"customervalidation",data,row.names=FALSE)
  #end body for other function

And content of SampleData.csv

Please help me in identifying the error what is missing here.

score 3 · Answer 1 · answered Jul 22 '16 at 14:07

The error is rather self-explanatory: The table already exists. If you look at the manual you'll see there are two options:

overwrite a logical specifying whether to overwrite an existing table or not. Its default is FALSE.
append a logical specifying whether to append to an existing table in the DBMS. Its default is FALSE.

You have to specify one of them as TRUE.

score 1 · Accepted Answer · answered Jan 08 '19 at 11:31

1

To avoid error write like this

     dbWriteTable(con,"customervalidation",data,row.names=FALSE,append = TRUE)

answered Jan 08 '19 at 11:31

Zeeshan

1,208
1
14
26

R language unable to insert in postgresql using postgresqlWriteTable

2 Answers2