Why can I not read my table although it is listed by dbListTables?

Question

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"

What does `dbExistsTable(con, "\"ecog\"")` return? I've had some issues with extra quotes with RPostgreSQL though it is probably something I've done wrong. — Brian Diggs, May 17 '12 at 19:01
Thanks for the post. I tried dbExistsTable(con, "\"ecog\"") and it still came back FALSE — Freda K, May 17 '12 at 21:02

Serenthia · Accepted Answer · 2017-09-04T11:38:38.390

14

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.

edited Sep 04 '17 at 11:38

answered Mar 06 '14 at 14:51

Serenthia

1,222
4
22
40

... and for reference, RPostgres doesn't necessarily respond in the same way. :/ – russellpierce Apr 11 '17 at 20:00
see rpostgres answer below – Arthur Yip Nov 10 '20 at 04:02

score 0 · Answer 2 · answered May 17 '12 at 19:18

0

I suspect a permissions issue. Please try the SQL commands via psql or another venue to rule out any backend-permissions issues.

Your commands work fine for me here:

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>

answered May 17 '12 at 19:18

Dirk Eddelbuettel

360,940
56
644
725

1

After some investigation, I have found that we can read and write to the **public** schema but not to other named schemas. All the files in both the public and named schema appear when I use the `dbListTables` command. – Freda K May 26 '12 at 19:38
See the `rpostgresql-dev` list on Google Groups -- this has come up before and as I recall somewhat vaguely has to do with lower- versus upper case spelling of schemas. Case matters for PostgreSQL; the SQL standard is silent on it. – Dirk Eddelbuettel Mar 08 '14 at 19:25

score 0 · Answer 3 · answered Nov 10 '20 at 04:03

0

The equivalent RPostgres syntax is

dbExistsTable(con, Id(schema = "schema_name", table = "table_name"))

answered Nov 10 '20 at 04:03

Arthur Yip

5,810
2
31
50

Why can I not read my table although it is listed by dbListTables?

3 Answers3