[SOLVED] How to avoid read_csv converting empty strings to NULL?

How to avoid read_csv converting empty strings to NULL?

I'm using DuckDB to read a CSV file that contains empty strings in some fields. However, DuckDB converts these empty strings to NULL.

CSV Sample Data:

"path","comment"
"","[{""comment"":""grand total"",""updatedByUser"":"",""dateTime"":""2025-03-27 09:08:38"",""threadStatus"":null,""threadAssignee"":null}]"
"East","[{""comment"":""Sub total"",""updatedByUser"":"",""dateTime"":""2025-03-27 09:09:03"",""threadStatus"":null,""threadAssignee"":null}]"

Query I'm Using:

SELECT * FROM read_csv('${path}', delim=',', header=True, columns={"path":"VARCHAR","comment":"VARCHAR"});

I want empty strings ("") in the path column to remain empty strings, but they are instead being converted to NULLs.

How can I have these values remain empty strings?

Solution

By default, DuckDB will treat empty strings as NULLs upon import. This behavior can be modified using force_not_null:

Do not match values in the specified columns against the NULL string. In the default case where the NULL string is empty, this means that empty values are read as zero-length strings instead of NULLs.