数据库名称全为大写字母时，使用rPostgreSQL将表写入Postgresql [英] writing tables to Postgresql using rPostgreSQL when the database name is all capital letters

问题描述

我在PostgreSQL中有一个数据库，全部都名为 DATA 。当我尝试使用RPostgreSQL将R data.frame写入此数据库时，如下所示：

I have a database in PostgreSQL which is named DATA in all caps. When I try to write an R data.frame to this database using RPostgreSQL like so:

library(RPostgreSQL)
con <- dbConnect(PostgreSQL(), host="myhost", 
                 user= "postgres", password="myPass", dbname="DATA")
dbWriteTable(con, "test", myDf)

我收到以下错误：

Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  no schema has been selected to create in
)
[1] FALSE

但是我注意到，如果我去PostgreSQL并将数据库名称更改为 data （小写），然后更改脚本以调用 dbname = data 然后它就像一个魅力。

However I notice that if I go to Postgresql and change the database name to data (lower case) and then change the script to call dbname="data" then it works like a charm.

我浏览了rPostgreSQL的文档，并且唯一提到的大小写与字段名区分大小写有关。

I looked through the documentation for rPostgreSQL and the only mention of case I saw had to do with field names being case sensitive.

所以我的问题是：

1. 这是预期的行为吗？ li>
   
2. 在我的情况下，我控制数据库，因此可以随意重命名数据库。如果无法将数据库重命名为所有小写字母，该如何解决？

1. Is this behavior is expected?
2. In my situation I control the DB so I can rename the database at will. How would I work around this behavior if I could not rename the database to all lower case?

推荐答案

表肯定存在大写问题。我们认为现在可以处理：
尝试将其引用为 DATA，它应该可以通过。未引用的表标识符全部变为小写。

There were definitely issues with tables in upper-case. In think we handle that now: Try quoting it as "DATA" and it should go through. Unquoted table identifier all get lower-cased.

您的问题是整个数据库都是大写的。它甚至可以使用引号，甚至可以使用'\ DATA\'作为dbConnect的参数。

Your issue is having the entire database in uppercase. It may also work with quoting, maybe even with '\"DATA\"' as an argument to dbConnect.

否则，列表中的可复制示例是最好的，如果运气好的话，Tomoaki将为您解决问题。

Otherwise, reproducible examples on the list are best, and with some luck, Tomoaki will find a fix for your problem.

哦，我们将其拼写为程序包：带有大写arrrrrrr的RPostgreSQL，尤其是今天在像piRate日这样的谈话中。

Oh, and we spell it like the package: RPostgreSQL with capital arrrrrrr, especially today on talk like a piRate day.

编辑：当前版本似乎根本没有问题在Ubuntu 11.04上：

Looks like there is simply no issue with current versions on Ubuntu 11.04:

首先，创建 DATA

edd@max:~$ createdb DATA
edd@max:~$ psql DATA
psql (8.4.8)
Type "help" for help.

DATA=# \q
edd@max:~$ 

第二，在R中，连接并保存一些数据：

Second, and in R, connect and save some data:

R> library(RPostgreSQL)
R> con <- dbConnect(PostgreSQL(), host="localhost", user= "edd", 
+                   password=".....", dbname="DATA")
R> con
<PostgreSQLConnection:(21936,0)> 
R> dbWriteTable(con, "quicktest", cars)
[1] TRUE
R> 

第三，检查 DATA 中的内容：

Third, check for content in DATA:

DATA=# select * from quicktest limit 5;
 row_names | speed | dist 
-----------+-------+------
 1         |     4 |    2
 2         |     4 |   10
 3         |     7 |    4
 4         |     7 |   22
 5         |     8 |   16
(5 rows)

DATA=# 

查找对我很好。

这篇关于数据库名称全为大写字母时，使用rPostgreSQL将表写入Postgresql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！