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?
Thanks,
--Lee
Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center