DBIConnection methods dbExistTable(), dbReadTable(), dbWriteTable() do not perform as expected on non-public database schemas unless the options parameter is included in dbConnect(), as answered on stackoverflow, eg : dbConnect(drv, dbuser, dbaccess, host, db, port, options="-c search_path=myschema")
options parameter present
no
yes
dbReadTable(conn, 'myschema')
#ERROR °°
#OUTPUT OK
dbReadTable(conn, 'myschema.mytable')
#ERROR °°
#ERROR °°
dbExistsTable(conn, 'myschema')
#TRUE °
#TRUE
dbExistsTable(conn, 'myschema.mytable')
#FALSE
#FALSE
° public schemas only
°°
Error in postgresqlExecStatement(conn, statement, ...) :
RS-DBI driver: (could not Retrieve the result : ERROR: the relation « [myschema.]mytable » doesn't exist
LINE 1: SELECT from "[myschema.]mytable"
^
)
Error in names(out) <- make.names(names(out), unique = TRUE) :
attempt to set an attribute on NULL
In addition: Warning message:
In postgresqlQuickSQL(conn, statement, ...) :
Could not create execute: SELECT from "[myschema.]mytable"
dbGetInfo(conn) does not distinct the difference if the argument options is used and if it is not.
If one wants to work with public schemas on the same database dbConnect() has to be called without the options.
These connection details are not present in the dbConnect {DBI} documentation. Adding an example in the docs would avoid encountering errors during connections to non-public schemas.
DBIConnection methods dbExistTable(), dbReadTable(), dbWriteTable() do not perform as expected on non-public database schemas unless the
options
parameter is included in dbConnect(), as answered on stackoverflow, eg :dbConnect(drv, dbuser, dbaccess, host, db, port, options="-c search_path=myschema")
dbGetInfo(conn) does not distinct the difference if the argument
options
is used and if it is not.If one wants to work with public schemas on the same database dbConnect() has to be called without the
options
.These connection details are not present in the dbConnect {DBI} documentation. Adding an example in the docs would avoid encountering errors during connections to non-public schemas.
Might it be relevant to post this issue on R Database Interface ?