CODEWITHSUNDEEP
sqlpostgresqlpostgis
dharv
dharv

Reputation: 51

Skip over lines that cause errors in PostgreSQL Copy Command

I get a UTF encoding error when trying to copy data from a csv file

Error invalid by sequence for encoding...
SQL State: 22021 
Context Copy mydatabase line 165

I am wondering if it is possible to skip over the line(s) that throws the error. I am aware that is possible to skip the first line using the header command but the line I get isn't in the first row. I'd like to find a solution inside of Postgres rather than one that has me going into another program.

Upvotes: 5

Views: 9822

Answers (2)

Baso
Baso

Reputation: 21

You could use PGLoader, a data loading tool for PostgreSQL. The COPY command is a single transaction so either all the data will get imported or none of it will. PGLoader has a transaction behaviour, it will keep a separate file of rejected data, but continue trying to copy good data in your database.

Here the repository: https://github.com/dimitri/pgloader

Upvotes: 2

Politank-Z
Politank-Z

Reputation: 3729

Unfortunately, that functionality hasn't been integrated into PostgreSQL. The quickest workaround I know of is to use sed to filter out bad input lines. If you really must keep the solution PostgreSQL-based, I know of no alternative to using PL/pgsql or another procedural language interface to read in your data.

Upvotes: 2

Related Questions