Import CSV to retool database gives error on null columns

[retool_user]

• Goal: I want to be able to import a CSV that has blank values for some rows in a nullable number column

• Steps: create a retool datase. add a column with a number type and let it be nullable. Create a csv which contains that column, add two rows, one with a value in the column and one without a value. import the csv into this table. Retool gives an error for the empty column.

• Details: this is using the retool database

• Screenshots:
  

  
  
  

[Augustine_Whitehouse]

Do you get the same error if you set a Default value of 0 in the column settings? Also are you doing this programmatically? And if so, can you share your code snippet? Cheers,

[retool_user]

CSV import is done within Retool's internal database screen:

Good idea on setting a default value, unfortunately that didn't help though. Still get the same error.

[jg80]

Saw something similar before here - In your CSV to you have ,, or ,"", for the column (you need to have ,,)?

[retool_user]

Here is the csv:

column_one,column_two,column_three
1, 888, 123
2,,

The second row should import Null for column_two and column_three, but instead it hits this error.

[bobthebear]

@jg80

Saw something similar before here

I think @Kabirdas workaround from the same thread is the simplest Band-Aid to this problem.

@Kabirdas

Passing an empty value for a particular cell (i.e. ,, ) should read as null for "date" columns, however, for "enum" or other text columns the value is indistinguishable from the empty string and gets read as the latter.

he only mentions that text columns, other than 'date', will read null as "" but from what you're saying it sounds like null integer does the same. have you tried this with Nullable as false or with INT8?

its a bit outside the box, but you could also try with Number type as float and PostgreSQL type as FLOAT4, the db should implicitly cast integers to floats on insert statements. floats have 3 special values no other numeric type has, specifically NaN and what you might find is that when Retool tries to insert "" into a float column PostgreSQL casts it to NaN instead of throwing that error. as long as you aren't doing complex math or maybe lots of comparisons then I wouldn't think the performance hit would be too bad