CODEWITHSUNDEEP
postgresqlnullcopy
Gunther Schadow
Gunther Schadow

Reputation: 1739

PostgreSQL \copy from ... with CSV null as '' still imports empty strings instead of NULLs

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?

Upvotes: 1

Views: 1782

Answers (1)

Gunther Schadow
Gunther Schadow

Reputation: 1739

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.

Upvotes: 2

Related Questions