PostgreSQL COPY CSV with two NULL strings

Question

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.

e.g.

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?

Answer 1

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')