Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.8k views
in Technique[技术] by (71.8m points)

postgresql - How to ignore errors with psql copy meta-command

I am using psql with a PostgreSQL database and the following copy command:

COPY isa (np1, np2, sentence) FROM 'c:Downloadsisa.txt' WITH DELIMITER '|'

I get:

ERROR:  extra data after last expected column

How can I skip the lines with errors?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

You cannot skip the errors without skipping the whole command up to and including Postgres 14. There is currently no more sophisticated error handling.

copy is just a wrapper around SQL COPY that channels results through psql. The manual for COPY:

COPY stops operation at the first error. This should not lead to problems in the event of a COPY TO, but the target table will already have received earlier rows in a COPY FROM. These rows will not be visible or accessible, but they still occupy disk space. This might amount to a considerable amount of wasted disk space if the failure happened well into a large copy operation. You might wish to invoke VACUUM to recover the wasted space.

Bold emphasis mine. And:

COPY FROM will raise an error if any line of the input file contains more or fewer columns than are expected.

COPY is an extremely fast way to import / export data. Sophisticated checks and error handling would slow it down.

There was an attempt to add error logging to COPY in Postgres 9.0 but it was never committed.

Solution

Fix your input file instead.

If you have one or more additional column in your input file and the file is otherwise consistent, you might add dummy columns to your table isa and drop those afterwards. Or (cleaner with production tables) import to a temporary staging table and INSERT selected columns (or expressions) to your target table isa from there.

Related answers with detailed instructions:


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...