10 ms·

>For example, Oracle database won’t allow you to have an empty string. Anytime Oracle database sees an empty string, it automatically converts the empty string

• johannes1234321 7y ago
  Right, I am a fan of SQL NULl as well. It is nicely consistent - anything NULL in - you get NULL out. Clearly telling that you get undefined data. Silently converting to empty string, zero, or therelike would eventually return garbage for harder to debug reasons. That Oracle behavior annoys me each time, though.
  • tabtab 7y ago
    Re: Silently converting to empty string, zero, or therelike would eventually return garbage for harder to debug reasons. It's never been a problem with strings in my many decades of experience, unless somebody does something which I consider poor system engineering. Nearby I invited a solid use-case illustrating a real string need.
• Starwatcher2001 7y ago
  I like nulls too and think they make perfect sense, especially with numeric fields. Suppose we have an "age" field, but don't actually know the age of the person, null makes perfect sense. Otherwise we'd have do do something like using a "magic number" like 0, -1, or a separate field altogether to indicate an unknown value. Granted, they do need some handling in queries.
  • ilogik 7y ago
    except that age = 0 is valid if your database has toddlers. Hell, even -1 makes sense if you're running a query to get the age of someone at a specific time. this is why you should try to use NULLs whenever possible
• ak39 7y ago
  AFAIK, this Oracle "feature" is only true for columns marked as "not nullable". So if you attempt to insert an empty sting ("") into a not-nullable column, it will fail. All other relational databases differentiate between empty strings and NULL.
  • shawnz 7y ago
    It is also true for nullable columns.
  • Sean1708 7y ago
    Nah, it's true for all columns: SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 3 15:56:30 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production SQL> create table foo(fook varchar2(10) not null); Table created. SQL> insert into foo values (''); insert into foo values ('') * ERROR at line 1: ORA-01400: cannot insert NULL into ("SPORTSBOOK_DOCK"."FOO"."FOOK") SQL> create table bar(bark varchar2(10)); Table created. SQL> insert into bar values (''); 1 row created. SQL> insert into bar values ('a'); 1 row created. SQL> insert into bar values (null); 1 row created. SQL> select * from bar; BARK ---------- a SQL> select * from bar where bark = ''; no rows selected
  • thenewnewguy 7y ago
    > if you attempt to insert an empty sting ("") into a not-nullable column, it will fail Yes, because it will convert the empty string into a NULL, and fail to insert that NULL into a non-NULLable column.
• beefield 7y ago
  You are not the only one. I get that there are some exotic and theoretical corner cases where nulls actually are problematic, but for the vast, vast majority of practical cases, nulls and three state logic are very useful. In my humble opinion.
• danso 7y ago
  There's a lot of real-world input situations in which I want NULL to indicate that a record field hasn't yet been collected, versus a record in which the answer to that field was "" – i.e. left blank by the respondent/data source. Sure I could have the front-facing data app auto-convert blank values to "N/A" or "(blank)", but that's unnecessary complexity.
  • SigmundA 7y ago
    Empty string isn't enough for this situation since it doesn't work for data types other than string, I actually prefer null vs undefined in javascript / json here, xml also has the concept of null vs undefined. Emtpy string vs null is not that useful if you have null and undefined.
    • giornogiovanna 7y ago
      Just to be clear, undefined is not valid in JSON. The only types in JSON are nulls, strings, numbers, arrays, booleans and objects.
      • cyphar 7y ago
        "undefined" in this context means that the JSON object has had the relevant key omitted rather than explicitly set to null. This is valid JSON (it just so happens that JavaScript handles this scenario by returning "undefined" as with all undefined variables).
        • giornogiovanna 7y ago
          Ah, right, my bad. In my defense, JavaScript treats a value of `undefined` differently from an unset field / variable (e.g. when testing `object.hasOwnProperty('field')`).
          • tabtab 7y ago
            JavaScript has too many "non-visible" value types in my opinion. This makes for both messy code and messy debugging.
• JMTQp8lwXL 7y ago
  >For example, Oracle database won’t allow you to have an empty string. It's also true for AWS' DynamoDB offering.
• shotashota 7y ago
  >I might be the only person who likes SQL nulls From my understanding, null values are bad because its a sign that the database design is flawed (See database normalizations) Perhaps you have a more practical experience?
  • RHSeeger 7y ago
    The fact that a database is not fully normalized is not a sign that it's flawed. In fact, there are cases where some tables being fully denormalized makes sense (although that's less common in my experience).
  • giornogiovanna 7y ago
    1. NOT NULL is very frequently used in schemas, because NULL is often an undesirable value (e.g. for a mandatory field). That doesn't make NULLs bad, and NULLs are still frequently used when you have optional fields or fields where NULL has some other special meaning. 2. NULLs are used by SQL functions and operators as an "unknown" value. So, for example, "NULL AND TRUE" is NULL, because we could substitute NULL with TRUE or with FALSE to get different results, but "NULL AND FALSE" is FALSE, because no matter what we substitute NULL with, the result will always be FALSE. 3. Clearly all these valid uses of NULL do not indicate "flaws" in the database design. 4. Database normalization isn't always a good thing, and beyond a certain level it's almost always a bad thing, so using normalization methods as a standard for whether something is "flawed" is probablyn ot the best idea. 5. No database normalization method, as far as I know, actually tries to eliminate NULLs, so I don't know what "(See database normalizations)" refers to. Can you clarify?
    • tabtab 7y ago
      Re: NOT NULL is very frequently used in schemas, because NULL is often an undesirable value Talking strings, usually if you don't want a null string, you also don't want blanks (white space) either. I'd like to see auto-trim in the standard, and also a minimum length specifier. We then wouldn't need to deal with nulls. A single min-length-when-trimmed-and-denulled integer value would replace a lot of repetitious hubbub in typical CRUD apps. D.R.Y. it! You'd have one attribute "cell" that would replace the equivalent of: IF length(trim(denull(inputValue,''))) < this_fields_min_length THEN raise_data_too_short_error(...); That's the way you'd want it done in the vast vast majority of CRUD systems (if not doing weird things).
• SigmundA 7y ago
  No I am tired of this WHERE (a = b) or (a IS NULL AND b IS NULL) Null should equal null like in every other programming language even SQL group by do null equals null which is even more inconsistent.
  • mumblemumble 7y ago
    Letting null equal null might work okay for a WHERE clause, but it turn JOINing into a terrible muddle. And I don't want null to behave different ways in different clauses.
    • SigmundA 7y ago
      Already behaves differently in GROUP BY, UNION, DISTINCT and PARTITION.
  • stetrain 7y ago
    Sounds like a disaster when trying to correlate Ids/keys between tables.
  • bloomer 7y ago
    NULL is similar to floating point NaN (aka not a number) which also has the same comparison operation NaN != NaN.
  • trts 7y ago
    where coalesce(a, '') = coalesce(b, '')
    • SigmundA 7y ago
      Normally can't use an index as every value has to be coalesced before comparison.
      • danaliv 7y ago
        You can index the results of functions (or other expressions) in at least some databases. PostgreSQL can do this, for example. CREATE INDEX idx ON tbl (coalesce(col, '')) Combining this with UNIQUE can make for some neat tricks.
• chias 7y ago
  I've recently gone and removed the "nullable" attribute from a bunch of SQL columns that previously had them (and arguably should have them), and the result has actually been rather pleasant. One interesting problem that arises when you use nulls is that it can be difficult to ensure people actually use them when it's appropriate to do so. Case in point I have a field that is essentially an optional positive integer, so obviously I made it a nullable unsigned int. A few years later there's a pretty even spread of nulls and 0s in there to indicate the same thing -- to a lot of consumers, they behave the same because their business logic basically says "if (foo->field) do stuff" which works either way. In the end I changed it to a non-nullable field using 0 as the null stand-in, which is semantically worse, but ended up making interesting searches over this data set a lot easier. On the one hand, perhaps the more correct answer would have been to yell at people putting 0s in when they should have been putting nulls in. On the other hand, we put constraints on fields for a reason...
• marcus_holmes 7y ago
  I also am a fan of nulls. But I also make very sure that I have defaults set up on every column for which a null value would make no sense. Which means pretty much every column that isn't an optional foreign key. I'm writing in Go, so my structs all have empty values unless specifically initialised. This does sometimes mean that I get null uuid's (0000-000000-00000-0000) inserted into tables, which Postgres doesn't understand as null and cheerfully returns as a valid uuid. This has been my only real pain with using nulls. I've contemplated modding the database driver to interpret nil-value uuid's as null, but that seems a little drastic. Anyone got any better ideas?
  • kaslai 7y ago
    My typical pattern is to use pointer types as a pseudo-optional type. There is the additional cost of dealing with a pointer on the Go side though, which can get cumbersome at times. `database/sql` does have an interface which lets you define your own marshaling code though. Using it is very simple and would let you marshal an all-zero UUID into NULL easily enough.
  • gnosek 7y ago
    Not sure how this integrates with Go's database drivers but from the Postgres side: 1. NULLIF(the_uuid, '0000-000000-00000-0000') https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-NULLIF https://www.postgresql.org/docs/current/functions-conditiona... 2. on insert/update rules to rewrite the uuid (probably using NULLIF) https://www.postgresql.org/docs/current/sql-createrule.html https://www.postgresql.org/docs/current/sql-createrule.html
  • dx034 7y ago
    pgx allows to pass pointers to indicate which values are null. That works with any arbitrary types and prevents from accidentially storing an empty values as null.
  • uncanny 7y ago
    You can use a type that marshals the Go zero value to database null and vice versa. https://godoc.org/github.com/jackc/pgtype/zeronull https://godoc.org/github.com/jackc/pgtype/zeronull provides a UUID type with that behavior.