7

The function dbplyr::in_schema() can not connect to tables with uppercase letters.

When I create a table in PostgreSQL.

CREATE TABLE public."OCLOC"
(
  cod_ocloc double precision NOT NULL,
  lab_ocloc character varying(255),
  CONSTRAINT pk_ocloc PRIMARY KEY (cod_ocloc)
);

INSERT INTO public."OCLOC"(
            cod_ocloc, lab_ocloc)
    VALUES (1, 'example');

Then I try to connect to the table using in_schema from R:

 con <- DBI::dbConnect(RPostgreSQL::PostgreSQL(), 
                  dbname = 'dbname', 
                  user = 'user', 
                  host = 'host', 
                  password = 'password')

 tbl(con, dbplyr::in_schema('public','OCLOC'))

Warns about the following error

Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  no existe la relación «public.ocloc»
LINE 1: SELECT * FROM public.OCLOC AS "zzz3" WHERE 0=1
                  ^
)

But when I try without in_schema connection works:

tbl(con, 'OCLOC')

Looks like a case-insensitive problem, This generates a problem when I use database with other schemas besides the public and there are table names with capital letters.

moodymudskipper
  • 46,417
  • 11
  • 121
  • 167
Diego
  • 2,196
  • 1
  • 21
  • 26
  • I am not sure if the problem you are having is related to `in_schema`, as this seems to have no problem supporting capitalisation: `> dbplyr::in_schema('public','OCLOC') public.OCLOC` Could the problem be with the driver instead? Did you try executing the produced query directly? `DBI::dbGetQuery(con, 'SELECT * FROM public.OCLOC AS "zzz3" WHERE 0=1')` – JAD Aug 23 '17 at 13:47
  • I also had issues with case sensitivity, connecting to Oracle. In the end I overwrote `DBI::dbWriteTable` and `DBI::dbReadTable` so they convert all queries and table names to uppercase before running. `dbplyr` is amazing but quite new and full of surprises, so I found (after half a day trying and not succeeding to rbind) it's safer to standardize (and minimize) the usage as much as possible. – moodymudskipper Sep 18 '17 at 22:18

1 Answers1

3

I have found the solution as follows: adding "" inside ''

tbl(con, '"OCLOC"')
Scarabee
  • 5,437
  • 5
  • 29
  • 55
Diego
  • 2,196
  • 1
  • 21
  • 26