Schemas not working in dbWriteTable with postgres

[latot]

Hi, Here a weird problem with the function, for some reason, is writing in the scheme...

dbWriteTable(con, Id(schema="other", table="tmp"), value=t)
[1] TRUE

Now checking postgres:

\dt
....
public  | "other"."tmp"              | table | admins
....

Is writing a table called "other"."tmp" inside the public scheme.....

Tested with SQL, Id and schema.table in table name methods.

https://rdrr.io/cran/DBI/man/dbWriteTable.html

R version 4.2.0 (2022-04-22)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Gentoo Linux

Matrix products: default
BLAS:   /usr/lib64/libblas.so.3.10.0
LAPACK: /usr/lib64/liblapack.so.3.10.0

locale:
 [1] LC_CTYPE=es_CL.utf8       LC_NUMERIC=C             
 [3] LC_TIME=es_CL.utf8        LC_COLLATE=es_CL.utf8    
 [5] LC_MONETARY=es_CL.utf8    LC_MESSAGES=es_CL.utf8   
 [7] LC_PAPER=es_CL.utf8       LC_NAME=C                
 [9] LC_ADDRESS=C              LC_TELEPHONE=C           
[11] LC_MEASUREMENT=es_CL.utf8 LC_IDENTIFICATION=C      

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] RPostgreSQL_0.7-3 DBI_1.1.2        

loaded via a namespace (and not attached):
[1] compiler_4.2.0

Thx.

[krlmlr]

Thanks. It seems that with RPostgreSQL you can't use this syntax. You could try RPostgres, or adapt the dbWriteTable() call to something that RPostgreSQL understand.

[latot]

Hi, after research and tests, RPostgreSQL works with:

dbWriteTable(con, c("a_schema", "a_table"), value=t)

How DBI try to act betwen R and RDBMS, I think this need a little of work? to get a standard way to specify the schemas, I don't know if this is about DBI or for example RPostgres.

Thx!

[latot]

Mmmm, with dbReadTable works with DBI::Id, with vector and SQL fails.

[krlmlr]

Thanks. Does dbWriteTable() works with DBI::Id too?

[latot]

Hi, as posted above, DBI::dbWriteTable only works with c(schema, table), does not works with SQL nor Id .

[krlmlr]

RPostgreSQL may behave differently. Happy to help with RPostgres if it behaves in any unexpected way.