I want to \COPY large CSV file to Postgres 10, and various types of error is expected from malformed rows i.e. extra quotes, additional column expected etc.
I would like to run \COPY command, and on failure the offending row is written to log for manual correction. \COPY is then rerun from the next row until new failure.
At the end, failing rows will be corrected manually, and added manually to the table.
I hope to get solution on this, combining \COPY and bash script if necessary.
Best Answer
Postgresql
\copy
simply fails on error and aborts the entire import. There's no option to skip malformed lines or invalid column contents.You need to use a program like pgloader that is built on top of COPY and adds the ability to filter out errors. As explained in its manual: