在 PostgreSQL 中使用 r sf::st_write 到非公共模式 [英] Using r sf::st_write to non-public schema in PostgreSQL

问题描述

我正在尝试将空间表写入一个非 PostgreSQL 数据库中默认公共架构的架构.

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)

但这会将我的表添加到名为 "myOtherSchema"."myCycle" 的公共模式中.

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

上面也试过...

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

...替换 st_write，这导致 myCycle 被写入公共架构.

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

我做错了什么?

会话信息:

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

在 Centos 7 操作系统上运行 PostgreSQL 11.1.

Running PostgreSQL 11.1 on Centos 7 OS.

推荐答案

发生这种情况是因为您通过包 RPostgreSQL 连接到数据库，但用于指定表和模式的语法与使用包 RPostgres 建立的连接.您可以使用以下方法解决此问题:

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

这篇关于在 PostgreSQL 中使用 r sf::st_write 到非公共模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！