rquery: Connect to specific schema in Postgres DB

user_10994855 0
Viewed 106

The rquery package has been out for some time now, but the documentation is still very sparse. There isn't even a tag yet in SO, this question will create it.

Maybe there is someone who can help me nevertheless.

I want to connect to a schema in my Postgres-DB via rqueryto read the data into R with all the speed it promises.

Using this code it works with all the tables in the public-schema.

library(RPostgres)
library(rquery)

con <- dbConnect(RPostgres::Postgres(),
                 host = #####,
                 dbname = #####,
                 user = #####,
                 password = ######)

df <- db_td(con, "tablename")  %.>%
        execute(con, .)

Now when I want to access a table in a specific schema db_td() has the argument qualifiers = which is an

optional named ordered vector of strings carrying additional db hierarchy terms,such as schema

So I did:

db_td(db, "tablename", qualifiers = c(schema = "schema"))

But:

Error in result_create(conn@ptr, statement) : Failed to prepare query: FEHLER: Relation »tablename« existiert nicht LINE 1: SELECT * FROM "tablename" LIMIT 1

So the qualifiers = argument seems to be completely ignored.

My question is thus pretty basic:

How can I connect to a schema in a PostgresDB via rquery?

rquery r
2 Answers

all my attempts to solve this "within" rquery seem to fail miserably, but you can work around it by doing something like:

dbExecute(con, "SET search_path = foo_schema, public;")

before you run db_td.

I think it's caused by rq_colnames doing:

paste0("SELECT * FROM ", quote_identifier(db, table_name), 
        " LIMIT 1")

and hence not doing anything with its qualifiers, at least this matches the error I get back.

maybe report a bug/issue with rquery if this isn't enough

user_13583080

I have created an issue on github. So far regular rquery indeed doesn't have schema ability. The development version of rquery (1.3.4) however has, as of today, basic schema ability.

To be installed via:

library(devtools)

install_github("WinVector/rquery",  host = "https://api.github.com")

Here's a small instruction. Seems to have been inteded to work just as I was trying in my question.

Be careful though, rquery hasn't been fully tested in schema-mode and some things might not work.

EDIT: rquery now has full schema support.

user_109948550
Related