rquery: Connect to specific schema in Postgres DB

Question

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?

not used this package, but did you try just using "schema.tablename" for the table_name? — Sam Mason
– Sam Mason, Commented Jun 4, 2019 at 12:44
Yes I have. I get the same error however. Probably because it's looking for schema.tablename inside the public schema. — Humpelstielzchen
– Humpelstielzchen, Commented Jun 4, 2019 at 12:46

Sam Mason · Accepted Answer · 2019-06-04 13:21:07Z

2

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

answered Jun 4, 2019 at 13:21

Sam Mason

16.5k1 gold badge49 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Humpelstielzchen Over a year ago

Thanks! Yes I was aware of that workaround. I'm about to create an issue on github. If I get an answer I'll post it here.

Humpelstielzchen · Accepted Answer · 2019-07-16 05:20:06Z

2

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.

edited Jul 16, 2019 at 5:20

answered Jun 6, 2019 at 12:24

Humpelstielzchen

6,4613 gold badges17 silver badges36 bronze badges

Collectives™ on Stack Overflow

rquery: Connect to specific schema in Postgres DB

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related