2

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

1
  • I guess you're looking at the wrong option in the docs. You say there are null fields in the files, but in the default text format nulls are expected to be represented by \N by default, so you might want to change that.
    – mustaccio
    Jul 11, 2022 at 16:57

1 Answer 1

3

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.

1
  • This appears to have fixed it. Thank you and I appreciate the detailed explanation. I knew it was something down in the weeds.
    – raindog308
    Jul 11, 2022 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.