I am trying to read a table into R using RPostgreSQL and R v2.14.2.
My version of RPostgreSQL is listed as 0.3-2, downloaded 16-May-2012.
My version of DBI is listed as 0.2-5, downloaded 16-May-2012.
I can open the database, and list the tables. The table I want to open is clearly present, however, when I try to read it, I get an error message. I am unsure if the error is in my code or in the way the database is set up.
library(RPostgreSQL)
# Loading required package: DBI
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, host = 'freda.freda.com', dbname = 'test', user = 'fredak', password = 'xxxx')
dbListTables(con)
# [1] "chemistry”
# [2] "ecog”
# [3] "hematology"
dbExistsTable(con, "ecog")
# [1] FALSE
MyTable <- dbReadTable(con, "ecog")
# Error in postgresqlExecStatement(conn, statement, ...) :
# RS-DBI driver: (could not Retrieve the result : ERROR: relation "ecog" does not exist
# LINE 1: SELECT * from "ecog"
# ^
# )
# Error in names(out) <- make.names(names(out), unique = TRUE) :
# attempt to set an attribute on NULL
# In addition: Warning message:
# In postgresqlQuickSQL(conn, statement, ...) :
# Could not create executeSELECT * from "ecog"
If wanting to interact with a table that's in a named schema, use the following (unintuitive) syntax:
dbExistsTable(con, c("schema_name", "table_name"))
[1] TRUE
This works despite dbListTables(con) returning all table names without their associated schemas.