Using COPY, ignore "invalid format" integers (or set to NULL)
I'm using COPY to load csv files. In a certain integer column, some rows have value "----" instead of NULL or 0. This causes my input records to be rejected (Invalid integer format '----' for column)
Is there a way for me to have them insert anyway, setting the value to 0 or NULL in the COPY statement ? I would prefer to avoid having to edit the csv itself.
I was thinking maybe one of these solutions :
- Specify a different parser for this columns only
- Force trim or replace those characters
- Specifiy all incorrect integers should be read as NULL instead of rejected
- Something else ?
But I have no idea which of those is possible and/or how to make it work.
Thanks in advance,