augmen / rpostgresql

Automatically exported from code.google.com/p/rpostgresql
0 stars 0 forks source link

ERROR: Extra data after last expected column #59

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago
So, I've been struggling to insert rows into a PostgreSQL table using 
dbWriteTable(). I keep getting the error in the subject line. I've had this 
problem for the last couple of days. I've searched the internet and have found 
all kinds of responses, but nothing that can solve my problem. Finally, I 
manually inserted dummy datat into the table. I read it with a dbRead(). Then 
write what I just read back to the table and still get the same error. 

> d2 <- dbReadTable(con, "tweets2")
> d2
  text name favourites_count followers_count retweet_count friends_count lon lat screen_name lang created_at
1    a    b                1               2             3             4   c   
d           e    f          g
> dbWriteTable(con, "tweets2", d2, append=T)
Error in postgresqlgetResult(new.con) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  extra data after last expected column
CONTEXT:  COPY tweets2, line 1: "1  a   b   1   2   3   4   c   d   e   f   g"
)
> R.version
               _                           
platform       x86_64-apple-darwin10.8.0   
arch           x86_64                      
os             darwin10.8.0                
system         x86_64, darwin10.8.0        
status                                     
major          3                           
minor          0.1                         
year           2013                        
month          05                          
day            16                          
svn rev        62743                       
language       R                           
version.string R version 3.0.1 (2013-05-16)
nickname       Good Sport                  
> packageVersion("RPostgreSQL")
[1] ‘0.4’

Am I using an old version of RPostgreSQL package? I downloaded the latest from 
cran. 

Original issue reported on code.google.com by vedacity...@gmail.com on 12 Nov 2013 at 2:42

GoogleCodeExporter commented 9 years ago
"tweets2" is not likely be created by dbWriteTable() and it is not in the scope 
of dbWriteTable() to append to a table that have different structure than 
created by dbWriteTable().  

The mismatch in the number of column is probably due to the row.names and 
row.names=F option may help.

The latest svn version might work better, as the column names are transmitted 
and thus the order may differ between the data frame and database definition.

Anyway, reading something from the relational database adding as is again 
violates RDB principle and should cause error.

Original comment by tomoa...@kenroku.kanazawa-u.ac.jp on 12 Nov 2013 at 7:33

GoogleCodeExporter commented 9 years ago
Adding row.names=F fixes the problem. Thanks much for your help!

You're right, "tweets2" was not created by dbWriteTable(). Also, the code above 
(to read from a table and write the data I just read back to the db) is simply 
a test I did to eliminate the possibility that the actual data I was reading 
from another source & trying to insert into the db didn't have any issues that 
caused this problem. 

Original comment by vedacity...@gmail.com on 12 Nov 2013 at 2:37