We all know that \copy command does not return anything when you load the data. The idea is to capture how many # of records got loaded into table through \copy command.
Here's a shell script that should work:
echo number of rows in input: $(wc -l data.in) ( echo "\copy test from stdin delimiter '|';" ; cat data.in ) | psql -v ON_ERROR_STOP=1 echo psql exit code $?
If the exit code printed is 0, everything went well, and the value printed by the first echo can be used to to indicate how many rows were inserted. If the printed exit code is non-zero, no rows were inserted, of course. If the exit code printed is 3 then the data being copied had some error.
From the docs: If the exit code printed is 1 or 2 then something went wrong in psql (like it ran out of memory) or the server connection was broken, respectively. Following facts play a role in the above script:
.) COPY (and hence \copy) expects the input records to be terminated by a newline. So counting the number of newlines in the input is a reliable way of counting the records inserted.
.) psql will exit with code 3 iff there's an error in script and ON_ERROR_STOP is set.
Note: This seems to not apply to the `psql -c "sql command"` construct.
# Example clean input
$ pgsql -c "create table test(a text,b int);" CREATE TABLE $ cat data.in column1|2 column1|2 column1|2 column1|2 column1|2 column1|2 $ echo number of rows in input: $(wc -l data.in); ( echo "\copy test from stdin delimiter '|';" ; cat data.in ) | psql -v ON_ERROR_STOP=1 ; echo psql exit code $? number of rows in input: 6 data.in psql exit code 0 # Example malformed input $ cat data.in column1|2 column1|2 column1|2c column1|2 column1|2 column1|2 $ echo number of rows in input: $(wc -l data.in); ( echo "\copy test from stdin delimiter '|';" ; cat data.in ) | pgsql -v ON_ERROR_STOP=1 ; echo psql exit code $? number of rows in input: 6 data.in ERROR: invalid input syntax for integer: "2c" CONTEXT: COPY test, line 3, column b: "2c" psql exit code 3
I hope this helps someone.
