How to connect to specific schema in Postgres DB?

[NompuK]

Hi,

this is my first time writing on GitHub. I hope this is the right place for my question.

I would like to connect to a specific schema in my Postgres database.

library(RPostgres)
library(rquery)

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

As of today I have no clue of rquery yet, but as far as I understood I create a handle of a table inside my database via db_td() and then proceed. And it works when I just want to access a table in my public schema.

To access a table in another schema the documentation shows the qualifiers = argument as

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

But when I do this:

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

I get this error:

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

Why is this?

Inside db_td() is the quote_table_name()- function, where qualifiers and tablename are put together and passed to mk_td().

`quote_table_name(con, "tablename", qualifiers = c(schema = "schema"))

[1] "\"schema\".\"tablename\""

It works, but from there the function seems to ignore q_table_name. Passing the schema together with the name as "schema.tablename" doesn't work either.

Maybe this is an error or I'm missunderstanding something, so my question is:

How can I connect to a specific schema in Postgres?

Thank you very much!

EDIT: On stackoverflow someone suggested the error to be in the rq_colnames-function at

q <- paste0("SELECT * FROM ",
              quote_identifier(db, table_name),
              " LIMIT 1")
  v <- rq_get_query(db, q)

Which seems plausible since it ignores the schema specification, but it can't be the whole story because then passing the schema with the tablename as "schema.tablename" should work.

[JohnMount]

Thanks for taking the time to file an issue, and sorry you are having trouble.

I haven't tried rquery a lot with schema qualifications- so I probably have a defect. Sorry.

I'll try and look at this soon.

[JohnMount]

I've looked into it. The current version of rquery (1.3.3) is unfortunately not yet schema ready.

I have started the conversion in a development branch- but it will probably take a couple of weeks. I was able to reproduce your issue and start to code to it: but the changes are not done and currently break a few things (so switching to the branch will not fix things yet).

With luck I should have some time to fix this in the next few weeks.

[JohnMount]

The development version of rquery (1.3.4) now has basic schema ability. schema support varies a lot from driver to driver, and there are issues (such as not being able to create temporary tables in non-temporary schemas). Example of how to set up the enhanced connection and work with schemas is now given here.

Please be warned that we have not used rquery in schema-mode in production yet. So there are likely to be some things that still need to be tested and extended.

Note to self: still need to schematize: rsummary(), join_controller(), ex_data_frame(), rlook(), complete_design(), lookup_by_column(), rstr(), and later cdata.

[NompuK]

Can confirm that it works now on rquery (1.3.4). Good job! Looking forward to rquery with "full" schema ability :relaxed:

[JohnMount]

rquery is now supporting schemas to about the quality that the underlying db connection does (good on RPostgres, okay on RPostgreSQL). The scheme isn't so much connecting to a schema (as the connection is something supplied to rquery, not built by rquery), but issuing schema qualified SQL. There are going to be some rough edges, and I'll accept issues on those.