dbExistsTable can't handle schema

[ilyaminati]

The dbExistsTable implementation cannot accept a schema name in any form, and is incompatible with the dbExistsTable API in RPostgreSQL.

This returns FALSE even when the table exists:

> con <- dbConnect(RPostgres::Postgres(), ...)
> dbExistsTable(con, "myschema.mytable")
[1] FALSE

This actually produces an error, even though it works in RPostgreSQL:

> con <- dbConnect(RPostgres::Postgres(), ...)
> dbExistsTable(con, c("myschema", "mytable"))
Error: length(name) == 1L is not TRUE

[krlmlr]

Thanks. Schema access will be different in RPostgres compared to RPostgreSQL, r-dbi/DBI#24 has details. In particular, dbExistsTable() requires a string.

In the meantime, consider setting the search_path database variable manually.

[baileych]

[Not sure anyone's watching this old bug, but it's open, so let's try in preference to repeating.]

It appears that the object with class "SQL" produced by dbQuoteIdentifier(pq_conn, 'some.string') surrounds the string with " which the grep() in dbExistsTable() sees. The object with class "SQL" produced by DBI::SQL(dbplyr::in_schema('some', 'string')) does not add the ", apparently because it's coming to DBI as an identifier. The absence of the double quotes causes RPostgres' dbUnquoteIdentifier() to balk. But since dbUnquoteIdentifier() actually wants a dot-separated schema.table pair, I'm not sure why it's so fussy about the presence of the quotes (which would prevent the identifier from being interpreted as such). Coming to this via dbplyr, it feels like a bug in dbUnquoteIdentifier(), so I'm asking here first. But I recognize it's fundamentally an incompatibility, and the fix could hypothetically be at either end.

[krlmlr]

This issue was raised before we even had dbUnquoteIdentifier() . Indeed, dbUnquoteIdentifier() could be a little less strict regarding the input it accepts -- currently it's designed to work only for results of dbQuoteIdentifier(conn, Id(...)). Can you please open a new issue in DBI?

The current implementation of dbplyr::in_schema() doesn't do any quoting. Perhaps it should?

[baileych]

Thanks. Done.

[krlmlr]

Thanks! Closing this as a duplicate.

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.