Issues with empty VARCHAR ("") and NOT NULL conditions

[LordBertson]

I am attempting to migrate a MariaDB database to Exasol and I have encountered an issue with the way Exasol handles empty VARCHAR (""). When I try to insert entries that do have empty strings in the column that has been defined as NOT NULL, these entries don't go through as Exasol internally converts "" to NULL.

I have considered dropping the NOT NULL conditions, inserting the rows and hoping for the best, but this has another issue though. Consider a scenario where I'd like to pull such rows from the database, we would want to use Python for this, the issue is the Pythonic None behaves vastly different to the "", for example, with respect to the function len() or as an iterated element of a for loop. I'd like to handle this without any changes to the application that uses the database. Is it possible to avoid this conversion? Alernatively, would it be possible to convert back to "" by the database upon reading?

[mwellbro]

Hi LordBertson,

I´m afraid it´s not so much a matter of conversion but rather the inherent way that Exasol treats empty strings (i.e. same as Oracle, which is empty string is equivalent to NULL ).
I think within Exasol we don´t have "convert back to "" " because an empty string as such does not exist - there might be some deep rooted switch where we could
change the "default behaviour" of the DB ( at the EXA-guys: I´m thinking "dbmode" ? ) but I haven´t tried something like that yet.

[geryoei]

In some installations I found it necessary to do a nvl(MyNulllableStringField," ") and trim it in the application (not in the query as it would again result in NULL). 

Maybe this helps.

[exa-Matze · Accepted solution]

A parameter to switch this behavior is not available (honestly, it isn't 😉)

As Malte mentioned, we have the same behavior as e.g. Oracle has: The empty string (string of length 0) does not exist. Such strings are automatically stored as NULL values.

(just to mention: this is why comparisons like string_column = '' will always return false since any comparison with NULL is evaluated to false)

Typically this is approached by converting the string with functions like NVL or COALESCE