PostgreSQL and COPY FROM

[Yoann M.]

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

 

[Michael Granger]

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 :
=20
db.exec("COPY t(a, b, c) FROM STDIN WITH DELIMITER '\t' csv QUOTE = ''''")
file =3D File.open("filename", "r")
file.each_line { |line| db.put_copy_data(line) }
db.put_copy_end
=20
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.
=20
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=20
half-inserted data
2. the data-transfer part of the copy is inside a begin/rescue=20
that calls #put_copy_end with an error-message argument if=20
an error occurs, which forces the COPY to fail with the=20
given message.

=46rom the relevant parts of the PostgreSQL API docs:

PQputCopyEnd

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

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

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

 

[Yoann M.]

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.