A blocking issue right now with the copy_to method which performs BULK INSERT operations, is how NULL values are treated.
As mentioned in issue #61, the treatment of empty string, which is the only way to provide a missing entry using a CSV file will be interpreted as an empty string, when the field is of NVARCHAR sort, but will be converted to default values for other fields such as 0.0 for FLOAT columns.
This is a deal breaker for a lot of use cases, including mine, where I have to insert float values that can potentially be NULL.
Solution
The solution is the addition of the optional null_string parameters that, when specified, will ensure any matching string in the input CSV file will be converted to a NULL value.
denisenkom
commented
7 years ago
Problem
A blocking issue right now with the
copy_tomethod which performs BULK INSERT operations, is howNULLvalues are treated.As mentioned in issue #61, the treatment of
empty string, which is the only way to provide a missing entry using aCSVfile will be interpreted as anempty string, when the field is ofNVARCHARsort, but will be converted to default values for other fields such as0.0forFLOATcolumns.This is a deal breaker for a lot of use cases, including mine, where I have to insert float values that can potentially be
NULL.Solution
The solution is the addition of the optional
null_stringparameters that, when specified, will ensure any matching string in the input CSV file will be converted to a NULL value.From an API standpoint, it is the same way
psycopgdoes it with thenullfield which is actually active by default (cf http://initd.org/psycopg/docs/cursor.html#cursor.copy_to)Please note that this solution will not affect in anyway the usage without this field from both a functional and a performance stand point.
@denisenkom and @cristianocoelho what do you think?
I am super excited, this is a my first pull request!