[R-sig-DB] How to write to database schema other than public using RPostgreSQL

[Xiaobo Gu]

On Sun, Mar 6, 2011 at 9:03 AM, Tomoaki NISHIYAMA
<tomoakin using kenroku.kanazawa-u.ac.jp> wrote:
> Hi,
>
> Why don't you try
>
> dbGetQuery(con, "set search_path to amber")
>
> dbWriteTable(con, "rtest", df, row.names=FALSE)
>
> ?
It does not work, dbExistsTable returns TRUE if tablename exists in
any of the schemas and if tablename is not with the format
schema.tablename;

> dbExistsTable(con, "rtest")
[1] TRUE
> dbExistsTable(con, "public.rtest")
[1] TRUE
> dbExistsTable(con, "amber.rtest")
[1] FALSE
>
So the idea is to let dbWriteTable work as dbExistsTable, if tablename
is the with format schema.talename then the table will be created in
the target schema, else it will be created in the default search path
schema.









>
> If table rtest exists in public you have to exclude public from
> the search path. Isn't it?
>
>
>
> (2011/03/05 22:56), Xiaobo Gu wrote:
>>
>> On Sat, Mar 5, 2011 at 9:16 PM, Tomoaki NISHIYAMA
>> <tomoakin using kenroku.kanazawa-u.ac.jp>  wrote:
>>>
>>> Hi,
>>>
>>> Did you use dbSendQeury() to change the search path or dbGetQeury() ?
>>
>> I have tried both, both failed, the last error is because there is
>> already table named rtest.
>>
>>> dbGetQuery(con, "set search_path to amber,public;")
>>
>> NULL
>>>
>>> dbGetQuery(con, "show search_path;")
>>
>>     search_path
>> 1 amber, public
>>>
>>> dbWriteTable(con, "rtest", df, row.names=FALSE)
>>
>> [1] FALSE
>> Warning message:
>> In postgresqlWriteTable(conn, name, value, ...) :
>>   table rtest exists in database: aborting assignTable
>>
>>
>>> As explained in the mail on 1 March on [R-sig-DB], dbSendQuery just sends
>>> the query but does not finish the query
>>> and other connection is created for later queries, which results as if
>>> the
>>> effect is not taken place.
>>>
>>> https://stat.ethz.ch/pipermail/r-sig-db/2011q1/001032.html
>>>
>>>
>>
>
>