27-05-2020 02:20 PM
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?
Solved! Go to Solution.
28-05-2020 03:50 PM
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
28-05-2020 12:48 PM
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.
27-05-2020 02:43 PM
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.
28-05-2020 03:50 PM
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
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.
Sign In