Error importing a file to PostgreSQL

Question

I'm trying to import a tab separated values file into a PostgreSQL database using the "COPY" command. The problem is that is fails on a line with the error message

ERROR: invalid byte sequence for encoding "UTF8": 0x00

The bad line can be found in this file.

It still fails when I try to import this single-line file.

I tried to open the file but it looks like a normal text file and I cannot find anyway to resolve this problem. The schema of the table looks like

CREATE TABLE osm_nodes (
  id BIGINT,
  longitude double precision,
  latitude double precision,
  tags TEXT
);

I use the following command to copy the file

cat bad_lines2 | psql -c "COPY osm_nodes FROM STDIN WITH DELIMITER ' '"

(Note: The delimeter above is the tab character)

I use (PostgreSQL) 9.2.3.

Thanks for your help.

Answer 1

I found the error. The text contains "\09" which was translated as a tab character and caused this problem. Each "\" should be escaped by "\" so that it can be inserted correctly.