1

I thought I knew PostgreSQL pretty well and have done this many times before, but I have here

\copy MyTable FROM ... WITH CSV NULL AS ''

and despite that a row such as:

"FooBar","","","1","0","","29505;29505",""

will not get its second column imported as NULL but as empty string!

SELECT count(1) FROM MyTable WHERE col2 IS NULL
== 0

SELECT count(1) FROM MyTable WHERE col2 = ''
>= 1

What am I doing wrong? Such a simple thing!

Here is a completely self-sufficient test to prove this:

CREATE TABLE Test(col1 text, col2 int);
\copy Test FROM PROGRAM 'printf '',\na,2\n''' WITH DELIMITER ',' NULL ''
SELECT (SELECT count(1) FROM Test WHERE col1 IS NULL) AS col1null,
       (SELECT count(1) FROM Test WHERE col2 IS NULL) AS col2null,
       (SELECT count(1) FROM Test WHERE col1 = '')    AS col1empty;
TRUNCATE Test;
\copy Test FROM PROGRAM 'printf ''"",""\n"a","2"\n''' WITH CSV NULL ''
\copy Test FROM PROGRAM 'printf ''"","0"\n"a","2"\n''' WITH CSV NULL ''
SELECT (SELECT count(1) FROM Test WHERE col1 IS NULL) AS col1null,
       (SELECT count(1) FROM Test WHERE col2 IS NULL) AS col2null,
       (SELECT count(1) FROM Test WHERE col1 = '')    AS col1empty;
DROP TABLE Test;

And here is how it runs:

foo=# CREATE TABLE Test(col1 text, col2 int);
SELECT (SELECT count(1) FROM Test WHERE col1 IS NULL) AS col1null,
       (SELECT count(1) FROM Test WHERE col2 IS NULL) AS col2null,
       (SELECT count(1) FROM Test WHERE col1 = '')    AS col1empty;
TRUNCATE Test;
\copy Test FROM PROGRAM 'printf ''"",""\n"a","2"\n''' WITH CSV NULL ''
\copy Test FROM PROGRAM 'printf ''"","0"\n"a","2"\n''' WITH CSV NULL ''
SELECT (SELECT count(1) FROM Test WHERE col1 IS NULL) AS col1null,
       (SELECT count(1) FROM Test WHERE col2 IS NULL) AS col2null,
       (SELECT count(1) FROM Test WHERE col1 = '')    AS col1empty;
DROP TABLE Test;CREATE TABLE
foo=# \copy Test FROM PROGRAM 'printf '',\na,2\n''' WITH DELIMITER ',' NULL ''
COPY 2
foo=# SELECT (SELECT count(1) FROM Test WHERE col1 IS NULL) AS col1null,
foo-#        (SELECT count(1) FROM Test WHERE col2 IS NULL) AS col2null,
foo-#        (SELECT count(1) FROM Test WHERE col1 = '')    AS col1empty;
 col1null | col2null | col1empty
----------+----------+-----------
        1 |        1 |         0
(1 row)

foo=# TRUNCATE Test;
TRUNCATE TABLE
foo=# \copy Test FROM PROGRAM 'printf ''"",""\n"a","2"\n''' WITH CSV NULL ''
ERROR:  invalid input syntax for integer: ""
CONTEXT:  COPY test, line 1, column col2: ""
foo=# \copy Test FROM PROGRAM 'printf ''"","0"\n"a","2"\n''' WITH CSV NULL ''
COPY 2
foo=# SELECT (SELECT count(1) FROM Test WHERE col1 IS NULL) AS col1null,
foo-#        (SELECT count(1) FROM Test WHERE col2 IS NULL) AS col2null,
foo-#        (SELECT count(1) FROM Test WHERE col1 = '')    AS col1empty;
 col1null | col2null | col1empty
----------+----------+-----------
        0 |        0 |         1
(1 row)

foo=# DROP TABLE Test;
DROP TABLE

So what can I do to get "" CSV as in text columns interpreted as NULL?

1
  • If you say null '' then COPY interprets empty fields as null, and fields containing empty quotes as empty strings. Commented Feb 22, 2020 at 0:12

1 Answer 1

2

I got it now

\copy Test FROM PROGRAM 'printf ''"",""\n"a","2"\n''' WITH(FORMAT CSV, DELIMITER ',', NULL '', FORCE_NULL(col1,col2))

Turns out the COPY syntax is very lax, but if you really want to make it work, you must use the WITH with parentheses and do it all correctly. Weird.

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.