'dbWriteTable()' does not write data out of public schema

[kamapu]

I am trying to implement the function RPostgreSQL::dbWriteTable() to create a table and a schema, and to populate the table with the content of a data frame in R, but I'm having issues that I assume as a bug in this package.

Here an example (note, you need to edit the connection for repeating it):

library(RPostgreSQL)

# Connection (requires edition for your system)
conn <- dbConnect(
        drv = "PostgreSQL",
        dbname = "your_dbname",
        host = "localhost",
        port = "5432",
        user = "your_user_name",
        password = "your_user_password")

# Custom names
schema_name <- "test_schema"
table_name <- "iris_db"

# Create new schema
Query <- paste0("CREATE SCHEMA \"", schema_name, "\";\n")
cat(Query) # Inspect the SQL-statement
dbSendQuery(conn, Query)

# Create new table
Query <- paste0("CREATE TABLE \"", schema_name, "\".\"", table_name, "\"(\n",
        "fid SERIAL PRIMARY KEY,\n",
        "sepal_l NUMERIC(2, 1),\n",
        "sepal_w NUMERIC(2, 1),\n",
        "petal_l NUMERIC(2, 1),\n",
        "petal_w NUMERIC(2, 1),\n",
        "species TEXT\n",
        ");\n"
        )
cat(Query) # Inspect the SQL-statement
dbSendQuery(conn, Query)

Until here everything is working perfect and I get the empty table in my database, nevertheless the next step (populating the table) is not working properly:

# Re-write column names in iris to match DB
colnames(iris) <- c("sepal_l", "sepal_w", "petal_l", "petal_w", "species")

# Attempt to insert the table to DB
dbWriteTable(conn = conn, name = Id(schema = schema_name, table = table_name),
        value = iris, append = TRUE, row.names = FALSE)

After it, the table in database is still empty and since I did not get any error message, I looked at the public schema and there is the imported data with the name "test_schema"."iris_db". This behavior is strange and not desirable for our purposes.

My solution was to use the function rpostgis::pgInsert() achieving our task:

library(rpostgis)
pgInsert(conn = conn, name = c(schema_name, table_name), data.obj = iris)

This solution is in the long term and for a wide audience not convenient because using rpostgis as dependency may require for users to install the whole GIS paraphernalia independent whether they work with GIS or not.

[tomoakin]

What is meant by "I looked at the public schema"? If the table is referred as test_schema.iris_db the iris_db table is in test_schema schema

https://www.postgresql.org/docs/13/ddl-schemas.html

[robozor]

My problem too. I need dbWriteTable with Id(schema = schema_name, table = table_name) and it doesn't work. Only the public schema works.

[tomoakin]

I now seem to better understand the issue. dbWriteTable() implementation of RPostgreSQL precedes Id() of DBI, and thus, Id() have not been supported yet. You are perhaps able to write to the intended schema with dbWriteTable(conn = conn, name = '"test_schema"."iris_db"', ...)

The Issue is better presented as feature request for support of ID class and dbQuoteIdentifier() and dbUnquoteIdentifier() functions.

[robozor]

It seems to be my fault. This is because the dbWriteTable function does not automatically create a schema if it does not exist. Therefore, writing the table to the public schema works. If the schema is created before the function is used, then the name can be easily specified via the DBI::Id format. It might be a good idea to extend the dbWriteTable() function to automatically create a schema if it doesn't exist yet. Once again, I apologize for the confusion.

The solution that worked for me:

destination_table <- DBI::Id(schema = Sys.getenv("DESTINATION_SCHEMA"),table = Sys.getenv("DESTINATION_TABLE"))

schema_exists <- DBI::dbGetQuery(db_connection_destination, paste0("SELECT EXISTS(SELECT 1 FROM information_schema.schemata WHERE schema_name = '", Sys.getenv("DESTINATION_SCHEMA"), "');"))[1,1] if (!schema_exists) { dbExecute(db_connection_destination, paste0('CREATE SCHEMA "',Sys.getenv("DESTINATION_SCHEMA"),'"')) }

RPostgreSQL::dbWriteTable(conn = db_connection_destination, value = as.data.frame(exportdata), name = destination_table, overwrite = TRUE)