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

Question

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.

I'd try connecting using package `RPostgres`: conn <- dbConnect(RPostgres::Postgres()), dbname = dbname, host = host, port = port, user = username, password = password)` — lbusett, Jan 15 '19 at 21:28
Did you try installing it (`install.packages("RPostgres")` ? — lbusett, Jan 15 '19 at 21:57
@lbusett ah ha. See what just google searching brings you. OK ran `conn <- dbConnect(Postgres(), dbname = dbname, host = host, port = port, user = username, password = password)` then `st_write(obj = cycle_hire, dsn = conn, Id(schema="roads_spatial", table = "myCycle")) ` and get `Error in st_write.sf(obj = cycle_hire, dsn = conn, Id(schema = "roads_spatial", : no st_write method available for dsn of class list` — mark, Jan 18 '19 at 14:32
Scratch that, my bad: @lbusett Ran `conn <- dbConnect(Postgres(), dbname = dbname, host = host, port = port, user = username, password = password)` then `st_write(obj = cycle_hire, dsn = conn, Id(schema="roads_spatial", table = "myCycle")) ` and success! Many thanks - I wasn't aware of `RPostgres`. Post a solution and I'll accept. — mark, Jan 18 '19 at 14:55
glad it helped. Was just thinking that with `RPostgreSQL` you could try something on the lines: st_write(obj = cycle_hire, dsn = conn, layer = c("myOtherSchema", "myCycle")) (see https://github.com/r-spatial/sf/issues/557) — lbusett, Jan 18 '19 at 16:34

score 12 · Accepted Answer · answered Jan 18 '19 at 17:27

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"))

Cedric · Answer 2 · 2020-12-03T11:01:40.130

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'

score 2 · Answer 3 · answered Jan 05 '22 at 07:59

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)

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

3 Answers3

Linked