Using r sf::st_write to non-public schema in PostgreSQL

user_2802810 0
Viewed 1301

I'm trying to write a spatial table to a schema that is not the default public schema in a PostgreSQL db.

library(sf)
library(DBI)
library(RPostgreSQL)
library(spData)

# PostgreSQL DB parameters
host <- "myHost" 
port <- 5432
username <- "myName"
dbname <- "myDb"
password <- "MyPassword"

# Connect to db  
conn <- dbConnect(PostgreSQL(), dbname = dbname, host = host, port = port, user = username, password = password)

st_write(obj = cycle_hire, dsn = conn, Id(schema="myOtherSchema", table = "myCycle")) # Write data to db - currently only writes to default schema

# Disconnect db
dbDisconnect(conn)

But this adds my table to the public schema with the name "myOtherSchema"."myCycle".

Also tried above with...

dbWriteTable(conn = conn, name = "myCycle", value = cycle_hire, Id(schema="mySchema"))

...substituted for st_write, which results in myCycle being written to public schema.

What am I doing wrong?

Session info:

R version 3.4.4 (2018-03-15)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows Server >= 2012 x64 (build 9200)

Running PostgreSQL 11.1 on Centos 7 OS.

sf r postgresql
3 Answers

This happens because you are connecting to the db via package RPostgreSQL, but the syntax used for specifying table and schema is that used with connections made with package RPostgres. You can solve this using: 

    require(RPostgres)
    conn <- dbConnect(Postgres(), dbname = dbname, host = host, port = port, 
                      user = username, password = password)
    st_write(obj = cycle_hire, dsn = conn, Id(schema="roads_spatial", table = "myCycle"))
user_68711350 
require(RPostgres)
    conn <- dbConnect(Postgres(), dbname = dbname, host = host, port = port, 
                      user = username, password = password)
    st_write(obj = cycle_hire, dsn = conn, Id(schema="roads_spatial", table = "myCycle"))

Using Postgres 12, I get a table "roads_spatial"."mycycle" in public which is not the intended result ?

So a simple roundabout way is to write to public and then use

dbExecute(
        conn,
        "ALTER TABLE myCycle SET SCHEMA roads_spatial")

 packageVersion("RPostgres") #[1] '1.2.0'
 packageVersion("DBI")       #[1] '1.1.0'
user_44948620

Also using Postgres 12, I initially ran into the same problem as @Cedric (a new table in public named the equivalent of "roads_spatial."mycycle". I was finally able to successfully create a new table named "mycycle" in the "roads_spatial" schema (or the equivalent in my database) with the following:

st_write(obj = cycle_hire, dsn = conn, layer = c("roads_spatial", "mycycle"), delete_layer = TRUE)
user_94592350
Related