PostgreSQL ESCAPE Quoting on TAB-delimited files: MySQL works, Pg doesn't, and I'm confused

Question

I'm working with some SEC data, which is available in very regularly formatted tab-separated files.

On MySQL, I can import these files as so:

CREATE TABLE import ( adsh text, tag text, version text, ddate text, qtrs text, uom text, dimh text, iprx text, value text, footnote text, footlen text, dimn text, coreg text, durp text, datp text, dcml text);

LOAD DATA INFILE '/pg/import/2011/Q2/num.tsv' INTO TABLE import FIELDS ESCAPED BY '' IGNORE 1 ROWS;

This works fine for every file. However, a similar command run on the Pg server does not work. In this case, I'm using 2011's Q2 'num' data from the .zip on that page. This file is a 40-column tab-separated file. I am running this command on the Pg server.

COPY import._2011_q2_num from '/pg/import/2011/Q2/num.tsv' DELIMITER E'\t' CSV HEADER;
ERROR:  unterminated CSV quoted field
CONTEXT:  COPY _2011_q2_num, line 830954: "0001193125-11-104388 DerivativeInstrumentsGainLossReclassifiedFromAccumulatedOCIIntoIncomeEffectiveP..."

According to wc -l, there are 830953 lines in that file. If I remove the last line, the error persists - it just says it's on like 830953, etc.

I think the problem is that there are multiple null fields which appear in the file as:

\t\t\t\t

(from od -c). I think Pg is looking at those consecutive tabs as escaping. The docs say:

ESCAPE...The default is the same as the QUOTE value (so that the quoting character is doubled if it appears in the data). This must be a single one-byte character.

OK, so I have to put some value for ESCAPE, even though nothing will ever be escaped in these files. There is no \b in the file, so I tried:

COPY import._2011_q2_num from '/pg/import/2011/Q2/num.tsv' DELIMITER E'\t' ESCAPE E'\b' CSV HEADER;

However, I got the same error.

Something funky with \b perhaps? I also tried tilde (which appears in some files, but I tried a file without it) and same error.

-- with E for ESCAPE byte
copy import._2011_q2_num from '/pg/import/2011/Q2/num.tsv' ESCAPE E'~' DELIMITER E'\t' CSV HEADER
-- without E
copy import._2011_q2_num from '/pg/import/2011/Q2/num.tsv' ESCAPE '~' DELIMITER E'\t' CSV HEADER

Based on my testing, about a quarter of the files fail, and the rest succeed. There's millions of lines in each file so it's not practical to hand-examine them, and I don't think the files are badly constructed as MySQL has no problem. I'm think I'm just not giving the right Pg syntax - ?

This is PostgreSQL 13.5 on Debian 11.3

Answer 1

According to the error message, your contents have " characters inside the fields.

In CSV this is allowed, but only on fields enclosed by quotes (" by default), and with inside-fields quotes being escaped (with another quote by default). If the fields are not enclosed you get the error mentioned in the question.

To work around that problem with the help of a character that doesn't exist in the contents, use the QUOTE option (do not use ESCAPE). By setting QUOTE to something else than ", then " will become a normal character, which is only what's needed with basic TSV files.