In R and PostgreSQL, use dbGetQuery to query tables inside schemas

user_10521389 0
Viewed 135

I'm in R trying to run a query on a local Postgres database that has some cable TV provider data in it. Using DBI::dbGetQuery, I'm attempting to run a simple query on this database, which has a simple nested tables-inside-schemas sort of structure. Here's my connection code:

pg <- dbDriver("PostgreSQL")

con <- dbConnect(pg, user = "postgres", 
                 password = "password",
                 host = "localhost",
                 port = 5432,
                 dbname = "db1")

All good so far -- if I run dbListTables(con), I get the right list of tables.

The tricky part is running the following query:

test_get <- dbGetQuery(con, "select * from provider.Provider_Composite limit 5;")

This throws the following error:

Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  relation "provider.provider_composite" does not exist
LINE 1: select * from provider.Provider_Composite limit 5;
                      ^

If I take the quoted portion of the R command (the actual postgres query, in other words) and paste it right into PGAdmin, for example, it pulls the correct table. But inside the dbGetQuery command in R, it doesn't work.

Any thoughts?

r postgresql database
2 Answers

This might help:

test_get <- dbGetQuery(con, 'select * from "provider.Provider_Composite" limit 5;')

(single quotes around the statement and double quotes around the table)

user_124561980

You need to double quote the table name because it has upper case letters.

test_get <- dbGetQuery(con, 'select * from provider."Provider_Composite" limit 5;')

The double quotes don't work around the schema.table address as suggested by @nina-van-bruggen. You have to quote each name string individually, if necessary. For that reason I try to only use lower case names in a PostgreSQL database.

user_47428890
Related