From: Michael Granger <ged@...>
Date: 2011-04-06T09:00:43+09:00
Subject: Re: PostgreSQL and COPY FROM

On Apr 1, 2011, at 4:50 AM, Yoann M. wrote:

> Hello,
> I use the 'pg' gem to send some data files to a postgresql database. I
> use the 'COPY ... FROM ...' query, on STDIN. Looks like this :
> 
> db.exec("COPY t(a, b, c) FROM STDIN WITH DELIMITER '\t' csv QUOTE ''''")
> file = File.open("filename", "r")
> file.each_line { |line| db.put_copy_data(line) }
> db.put_copy_end
> 
> It works. If I try to run a bad SQL query I get a PGError exception. If
> I try to run it with bad data files, I get no exception but the data is
> not (or not entirely) put in the database. The same data file with a
> copy command runned in psql will fail and display an error.
> 
> Is there a way to know when the copy fails from the ruby program ? and
> get the error message ?

There's an example of how to use COPY FROM in the samples/ directory of the gem:

  https://bitbucket.org/ged/ruby-pg/src/tip/sample/copyfrom.rb

Key parts to note:

1. the COPY FROM runs in a transaction so you won't get 
   half-inserted data
2. the data-transfer part of the copy is inside a begin/rescue 
   that calls #put_copy_end with an error-message argument if 
   an error occurs, which forces the COPY to fail with the 
   given message.

From the relevant parts of the PostgreSQL API docs:

  PQputCopyEnd

  Sends end-of-data indication to the server during COPY_IN 
  state. 

    int PQputCopyEnd(PGconn *conn, const char *errormsg);

  Ends the COPY_IN operation successfully if errormsg is NULL. 
  If errormsg is not NULL then the COPY is forced to fail, 
  with the string pointed to by errormsg used as the error 
  message.

--
Michael Granger <ged@FaerieMUD.org>
Rubymage, Architect, Believer
The FaerieMUD Consortium <http://www.FaerieMUD.org/>