writing tables to Postgresql using rPostgreSQL when the database name is all capital letters

Question

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)

I get the following error:

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

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.

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

So my questions are:

Is this behavior is expected?
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?

Dirk is no longer here · Accepted Answer · 2011-09-19 20:23:49Z

9

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.

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

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

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

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

First, create DATA

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

DATA=# \q
edd@max:~$

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>

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=#

Looking good to me.

edited Sep 19, 2011 at 20:23

answered Sep 19, 2011 at 18:41

Dirk is no longer here

370k60 gold badges668 silver badges742 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JD Long Over a year ago

'\"DATA\"' looks promising. I'll test that in a bit. Thanks, as always, for the super fast response.

JD Long Over a year ago

I'm running a version of rPostgreSQL which I built on Mac OS 10.7. It is possible that I built a previous version. I'll build from the latest source and test.

Chanti Over a year ago

@ Dirk Eddelbuettel: dbwrite table is very clear thank you. i have a small question regarding inserting dataframe into a table(database). I have a table with name "bank1" and it already contains some data. i have a dataframe in R with name "df1" which contains the same data (similar no of columns and column names). Now i want to update bank1 with df1. could you please tell me how can i do it

Collectives™ on Stack Overflow

writing tables to Postgresql using rPostgreSQL when the database name is all capital letters

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related