On 17/06/2013 16:44, Arran Cudbard-Bell wrote:
The idea is that a place-holder is better than silently expanding to an empty string. An empty string means that data which is supposed to be 4 columns (for example) is now suddenly 3. That's bad. No, because all values should be quoted (even integers and timestamps) and a "" or a '' is perfectly acceptable in the SQL world.
Aside: I think Postgres will reject a quoted string being used in an integer context.
If users have an issue with it they can do %{%{FOO}:-_}, inserting _ by default is magical, inconsistent and unexpected.
Besides, for SQL the proper behaviour would be %{%{FOO}:-NULL}, NULL being a value which is not known, anything else is arguably wrong. Yes, that is the correct way to insert an integer attribute which may or may not be present.
This is easy for integers but not easy for strings, where in theory you want a quoted value if it's present but an unquoted NULL if it is not. In practice, having to deal with NULL and empty string as distinct values is usually a pain, so I always write the empty string for missing attributes. And Oracle forces empty string to NULL in a varchar column. Regards, Brian.