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

[Tomoaki NISHIYAMA]

Hi,

> 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;

You don't show what table exists in reality.
The code for dbExistsTable checks only for current schema and  
tablename is just the
table name whethere or not tablename includes any number of periods.

What do you get with the following and do you compare the results  
with dbExistsTable?
dbGetQuery(con, 'select * from "rtest" LIMIT 0' )
dbGetQuery(con, 'select * from "public.rtest" LIMIT 0' )
dbGetQuery(con, 'select * from "amber.rtest" LIMIT 0' )
dbGetQuery(con, 'select * from public.rtest LIMIT 0' )
dbGetQuery(con, 'select * from amber.rtest LIMIT 0' )
-- 
Tomoaki NISHIYAMA

Advanced Science Research Center,
Kanazawa University,
13-1 Takara-machi,
Kanazawa, 920-0934, Japan


On 2011/03/06, at 13:18, Xiaobo Gu wrote:

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