使用RPostgreSQL写入特定的架构 [英] Writing to specific schemas with RPostgreSQL

问题描述

我正在使用 RPostgreSQL 来读写数据。从任何模式读取都可以正常工作，但是我无法写入非公开模式。例如，以下代码将表放置在 public 模式中，并将表命名为 myschema.tablex

I'm using RPostgreSQL to read and write data. Reading from any schema works perfectly, but I'm not able to write to non-public schemas. For example, the following code places a table in the public schema, with the name myschema.tablex

# write dataframe to postgres 
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, host="localhost", user="postgres", password="zzzz", dbname="mydatabase", port="5436")
if(dbExistsTable(con,"myschema.tablex")) {
  dbRemoveTable(con,"myschema.vkt_tablex")}
dbWriteTable(con,"myschema.tablex", dataframe, row.names=F)

我想做的是将表 tablex 放在模式 myschema中。我还尝试在连接中命名架构： dbname = mydatabase.myschema 并尝试使用参数 schemaname

What I want to do, is to place the table tablex in the schema myschema. I've also tried to name the schema in the connection: dbname="mydatabase.myschema" and trying the argument schemaname which I saw referred to in an earlier bug.

这些方法都不起作用，所以我想知道是否还有其他方法可以使用。

None of these approaches work, so I'm wondering if there is another method that I can use.

推荐答案

创建对象的默认模式由 search_path 。一种方法是相应地进行设置。例如：

The default schema where objects are created is defined by the search_path. One way would be to set it accordingly. For instance:

SET search_path = myschema, public;

我引用手册：

在创建对象时未指定特定目标
架构时，它们将放置在搜索
路径中列出的第一个架构中。如果搜索路径为空，则会报告错误。

When objects are created without specifying a particular target schema, they will be placed in the first schema listed in the search path. An error is reported if the search path is empty.

您也可以将其设置为角色的默认设置，因此将自动为该角色进行的每个连接设置该角色。更多：

You can also make this the default for a role, so it is set automatically for every connection made by this role. More:

• search_path如何影响标识符解析度和当前模式

• How does the search_path influence identifier resolution and the "current schema"

这篇关于使用RPostgreSQL写入特定的架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！