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

[Tomoaki NISHIYAMA]

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