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?

khampson · Accepted Answer · 2015-05-22 23:38:14Z

4

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

answered May 22, 2015 at 23:38

khampson

15.5k4 gold badges45 silver badges44 bronze badges

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

1 Comment

CharlieSmith Over a year ago

NULLIF could also be used (that would be more compact). e.g. NULLIF(col1, 'Not applicable') - that is a nice clean way. Many thanks.

Collectives™ on Stack Overflow

PostgreSQL COPY CSV with two NULL strings

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related