ghost commented 3 years ago

The Problem

If I use multiple schemata in postgreSQL, dbxInsert does not work properly when using the schema names for the table name.

Versions

dbx version 0.2.7
RPostgreSQL 0.6.2
R 3.5.2
PostgreSQL: 13.1 (debian buster)

Analysis

Using quoted table names leads to an error in postgreSQL if you need to specify a schema. As dbxInsert does not have a parameter for the schema, one can only try to abuse the table parameter for that. But when the table name is quoted by the sql tool (maybe inherited by dbx), postgreSQL does not find that as it interprets this as a full table name instead of a combined schema + table name.

Solution proposal

add an optional parameter schema to dbxInsert, defaulted to NA
quote the combination this way: "schema"."table", e.g. "myschema"."mytable" instead of "myschema.mytable"

Example to reproduce the failure

my_linux_shell (debian buster) $ createdb dbx

setup the database

create schema myschema;
create table mytable;
create table myschema.mytable(id serial primary key, content text not null);
insert into myschema.mytable(content) values ('unquoted');
INSERT 0 1

reproduce the error message in psql

henkej@dbx@[local]> insert into "myschema.mytable" ("content") values ('quoted');
FEHLER:  Relation »myschema.mytable« doesn't exist
ZEILE 1: insert into "myschema.mytable" ("content") values ('quoted')...

the correction in psql

henkej@dbx@[local]> insert into "myschema"."mytable" (content) values ('quoted');
INSERT 0 1

setup R

library(dbx)
library(RPostgreSQL)

df_records <- data.frame(content = c("quoted content"), stringsAsFactors = FALSE)

con <- dbx::dbxConnect(adapter = "postgres", dbname = "dbx")
dbx::dbxInsert(conn = con, 
               table = "myschema.mytable", 
               records = df_records)
dbx::dbxDisconnect(conn = con)

the error message in R

Fehler in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : FEHLER:  Relation »myschema.mytable« doesn't exist
ZEILE 1: INSERT INTO "myschema.mytable" ("content") VALUES ('quoted c...
                     ^
)

ankane commented 3 years ago

Hey @joerghenkebuero, thanks for the detailed report and proposed solutions! It looks like you can currently use:

DBI::SQL("myschema.mytable")
DBI::Id(schema="myschema", table="mytable")

I added some tests and will add a section to the readme as well.