Registered Member
|
Due to previous life of my data, some fields have a lot of NULL occurrences.
But when I erase a value in such a field I have just an empty field. How could I manage (the simpler would be to be able to enter NULL when erasing |
Moderator
|
Hi Robert,
You have presented the behaviour corrently: empty ("") i.e. zero-length Text values are inserted when text is deleted. NULLs are only used when I skip entering data in given "cell". For values of types other than Text, deleting the cell's content sets NULL. We basically have no other option for these types. Summing up, I have nothing against changing the proposed behaviour but we're talking about changing the default behaviour. I propose that everybody interested shares opinion here. The problem is that I can have a table where some cells already have empty text "" values. When I open the table using Kexi that changed the default behaviour, and when I delete some values in cells, I will stay with table that has some empty text "" values and some NULL values in the same column. This means a query that selects records with non-filled values should both search for NULLs and for "" values. Any pre-existing queries need to be adapted by the user... (See how nontrivial is handling NULLs, examples for MS Access but are generic enough: http://allenbrowne.com/casu-12.html) So I propose to file a wish "Add option to set NULL when value of Text type is deleted". PS: Related thing is that as I remember in April 2013 we added the following option to the Import CSV tool: "Import missing text values as texts" (instead of NULLs). PS2: Workaround for you, Robert: Close Kexi first to be 100% safe, and make a backup copy of the database. Then to change "" values to nulls in a table T and column C, open the database using admin tool (mysql or postgresql admin for server databases, and sqlite3 or sqliteman for .kexi sqlite-based file), and type: update T set C = NULL where C = ""; To change in the opposite direction, type: update T set C = "" where C IS NULL; |
Registered users: bancha, Bing [Bot], Evergrowing, Google [Bot], lockheed, mesutakcan, sandyvee, Sogou [Bot]