I have a source of csv files from a web query which contains two variations of a string that I would like to class as NULL when copying to a PostgreSQL table.
COPY my_table FROM STDIN WITH CSV DELIMITER AS ',' NULL AS ('N/A', 'Not applicable');
I know this query will throw an error so I'm looking for a way to specify two separate NULL strings in a COPY CSV query?
Best How To :
I think your best bet in this case, since
COPY does not support multiple
NULL strings, is to set the
NULL string argument to one of them, and then, once it's all loaded, do an
UPDATE that will set values in any column you wish having the other
NULL string you want to the actual
NULL value (the exact query would depend on which columns could have those values).
If you have a bunch of columns, you could use
CASE statements in your
SET clause to return
NULL if it matches your special string, or the value otherwise.
NULLIF could also be used (that would be more compact). e.g.
NULLIF(col1, 'Not applicable')