PostgreSQL and COPY FROM

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

Sorry for replying so late, and thank you so much for your link it’s
very helpful. With the example code, I can catch Postgresql status and
know if the COPY has failed or not (lines 73 and 74). I get a
PGRES_FATAL_ERROR when my datafiles are not ok, and a PGRES_COMMAND_OK
when everything went fine.

This solves my first issue, but my second issue still remains :
is there any way to get the postgresql error message about why the COPY
failed ? Inside psql, we get a very precise message including line
number, I’d like to display that.

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

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.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs