[R-sig-DB] dbWriteTable on virtual linux box

[Dirk Eddelbuettel]

On 28 February 2011 at 21:58, Lee Hachadoorian wrote:
| On 02/28/2011 07:20 PM, Tomoaki NISHIYAMA wrote:
| > Hi,
| >
| >> However, at a different
| >> location, I only have access to a Windows computer, so I am running R on
| >> Ubuntu on VirtualBox. On this setup, when I try the above code, I get:
| >
| >
| > What version of RPostgreSQL are you using?
| >
| > Is the PostgreSQL server working on the same virtualbox or are
| > you to communicate the PostgreSQL server running on the host
| > environment?
| >
| > If you are using an old version of RPostgreSQL, it cannot
| > dbWriteTable to other hosts, but the most
| > recent release version (0.1-7) should work.
| >
| > Another comment on your code:
| >> dbWriteTable(conn, "<schema_name>.<table_anme", df1, row.names = FALSE)
| >
| >
| > the table argument will only treated as the table name even if the string
| > contains one or more periods in current implementation.
| > If you want to write in non-default schema you need to change the
| > default schema beforehand.
| >
| > For future implementation, I am thinking of allowing
| > something like c("schemaname", "tablename"),
| > but not sure on how many people agree, and is not implemented anyway.
| Tomoaki,
| 
| You were right, I was using version 0.1-6. I upgraded to 0.1-7, and now
| it, but with the additional complication, as you describe, that table
| names are quoted. I can't figure out how to write to the desired schema.
| In SQL I would use
| 
| SET search_path = myschema;
| 
| But when I
| 
| dbSendQuery(conn, "SET search_path = myschema;")
| dbGetQuery(conn, "SHOW search_path")
| 
|      search_path
| 1 "$user",public
| 
| because the search_path only applies to the current session, and the
| session ends when dbSendQuery returns a result.
| 
| On the other hand
| 
| dbGetQuery(connLocalhost, "SET search_path = myschema; SHOW search_path;")
| 
|   search_path
| 1        myschema
| 
| but since dbWriteTable takes a table name (which it quotes) rather than
| a SQL statement, this approach can't be followed. So is there a way to
| write to anything other than the public schema in version 0.1-7?

Tomoaki detailed this in several examples here or on the rpostgresql-dev list
at https://groups.google.com/forum/?pli=1#!forum/rpostgresql-dev --- sorry
but I don't have the exact same thread handy.  

In short, behaviour is fairly consistent now given the dual constraint of
following the DBI interface for R as well as PostgreSQL pattern where
quoted/non-quoted tables make a difference.

Dirk

| Thanks,
| --Lee
| 
| -- 
| Lee Hachadoorian
| PhD Student, Geography
| Program in Earth & Environmental Sciences
| CUNY Graduate Center
| 
| _______________________________________________
| R-sig-DB mailing list -- R Special Interest Group
| R-sig-DB using r-project.org
| https://stat.ethz.ch/mailman/listinfo/r-sig-db

-- 
Dirk Eddelbuettel | edd using debian.org | http://dirk.eddelbuettel.com