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.
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:
To load data to PostgreSQL, pgloader uses the COPY streaming protocol. While this is the faster way to load data, COPY has an important drawback: as soon as PostgreSQL emits an error with any bit of data sent to it, whatever the problem is, the whole data set is rejected by PostgreSQL.
To work around that, pgloader cuts the data into batches of 25000 rows each, so that when a problem occurs it’s only impacting that many rows of data. Each batch is kept in memory while the COPY streaming happens, in order to be able to handle errors should some happen.
When PostgreSQL rejects the whole batch, pgloader logs the error message then isolates the bad row(s) from the accepted ones by retrying the batched rows in smaller batches. To do that, pgloader parses the CONTEXT error message from the failed COPY, as the message contains the line number where the error was found[...]
