Geek asks and answers

Why can't I read my table even though it is listed in dbListTables?

I am trying to read a table in R using RPostgreSQL and R v2.14.2.
My version of RPostgreSQL is listed as 0.3-2, downloaded from May 16 to 2012.
My DBI version is listed as 0.2-5, downloaded May 16, 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, an error message appears. I am not sure if the error is in my code or in how the database is configured.

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"
+3
source share
2 answers

If you want to interact with a table that uses the following (non-intuitive) syntax in a named schema:

dbExistsTable(con, c("schema_name", "table_name"))
[1] TRUE

, , dbListTables(con) .

+11

, . SQL psql , - .

:

R> library(RPostgreSQL)
Loading required package: DBI
R> drv <- dbDriver("PostgreSQL")
R> con <- dbConnect(drv, dbname="beancounter", user="edd", password="xxxxxx") 
R> dbListTables(con)
[1] "beancounter"   "cash"          "fxprices"      "indices"       "meta"
[6] "portfolio"     "portfoliosold" "stockinfo"     "stockprices"  
R> dbExistsTable(con, "cash")
[1] TRUE
R> dbExistsTable(con, 'cash')
[1] TRUE
R> dbExistsTable(con, 'Cash')
[1] FALSE
R> dbExistsTable(con, "Cash")
[1] FALSE
R> ccc <- dbReadTable(con, "cash")
R> dim(ccc)
[1] 24  7
R>
0

More articles:


All Articles