CODEWITHSUNDEEP
rdatabasepostgresql
logjammin
logjammin

Reputation: 1211

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

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?

Upvotes: 1

Views: 970

Answers (2)

andschar
andschar

Reputation: 3973

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.

Upvotes: 1

Nina van Bruggen
Nina van Bruggen

Reputation: 523

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)

Upvotes: 1

Related Questions