[R-sig-DB] dbWriteTable of RPostgreSQL can't insert data into PostgreSQL Server.

[Xiaobo Gu]

On Sun, Feb 6, 2011 at 6:43 PM, Tomoaki NISHIYAMA
<tomoakin using kenroku.kanazawa-u.ac.jp> wrote:
> Hi,
>
>> It seems there are other problems than encoding, the columns with
>> Chinese character have been removed from the sample data I sent, but
>> the remaining columns still can't get inserted into database server.
>
>
> Now, I got your first message, which somehow arrived much later.
> In my environment, transfer of your data.frame as GBK does not cause error
> while transfer as UTF8 does.
>
>> library('RPostgreSQL')
> Loading required package: DBI
>> load('ddwe.RData')
>> drv <- dbDriver("PostgreSQL")
>> con <- dbConnect(drv,
> +                      user=Sys.getenv("POSTGRES_USER"),
> +                      password=Sys.getenv("POSTGRES_PASSWD"),
> +                      host=Sys.getenv("POSTGRES_HOST"),
> +                      dbname=Sys.getenv("POSTGRES_DATABASE"),
> +                      port=ifelse((p<-Sys.getenv("POSTGRES_PORT"))!="", p,
> 5432))
>> dbGetQuery(con, "SHOW client_encoding;")
>  client_encoding
> 1            UTF8
>> dbWriteTable(con, "test1", samle1)
> [1] FALSE
> Warning message:
> In postgresqlWriteTable(conn, name, value, ...) :
>  table test1 exists in database: aborting assignTable
>> dbGetQuery(con, "drop table test1")
> NULL
>> dbWriteTable(con, "test1", samle1)
> Error in postgresqlgetResult(new.con) :
>  RS-DBI driver: (could not Retrieve the result : ERROR:  invalid byte
> sequence for encoding "UTF8": 0xa3
> HINT:  This error can also happen if the byte sequence does not match the
> encoding expected by the server, which is controlled by "client_encoding".
> CONTEXT:  COPY test1, line 80
> )
>> dbGetQuery(con, "SET CLIENT_ENCODING TO 'GBK'")
> NULL
>> dbGetQuery(con, "SHOW client_encoding;")
>  client_encoding
> 1             GBK
With client encoding set to GBK, the whole data frame can be inserted
into PostgreSQL 9.0.2 and Greenplum 4.0.4.0, thank you Tomoaki, Dirk
and Professor Brian.

There is another question, does dbWriteTable write the content of
data.frames into a csv file, and then copy it to the database server.
If so, where will the temp csv file created, can I set it to a
directory resides on fast disks.

>> dbWriteTable(con, "test2", samle1)
> [1] TRUE
>> dbGetQuery(con, "SELECT count(*) from test2")
>  count
> 1   101
> --
> Tomoaki NISHIYAMA
>
> Advanced Science Research Center,
> Kanazawa University,
> 13-1 Takara-machi,
> Kanazawa, 920-0934, Japan
>
>
>