I think the main lesson from this is you should use a NOT NULL constraint on just about every column of your database, since the behaviour of NULLs is weird and likely to indicate a problem with your data.
The idea with NULL is that it is not a value, it is the absence of value, like a field you didn't fill in a form. For example, if you ask two people their personal information, and neither specified their email address (email=NULL), you can't assume they have the same email address. And if you put a UNIQUE constraint on that email field, you probably don't mean that only one person is allowed to leave it blank: either you make it mandatory (NOT NULL), or you let everyone leave it blank.
The reason nullable and unique are rarely seen together is that unique is typically for keys, and generally, you don't want rows that have no key value. Also, putting a unique constraint on something that is not a key may not be the best idea. For example, if you don't intend to use email to uniquely identify people, what's the problem with two people having the same email?
One of the worst offenders was one API I was working with Optional<Something> could be null.
{"foo": null} and {}
Because they are different, right?
Option<Option<String>>
There are other ways to represent this in Rust, some of which might be clearer. This representation seems to be used most often if you have a type "MyType", and you want to automatically derive a type "MyTypePatch" using a macro.
What I'm wondering is how people who have an issue with different representations of absence would approach (or avoid?) situations requiring them.
Ideally you can force the use of the discriminator… but that depends on your type system
Where the "double absence" issue comes up in practice, it's usually in a context where it does make sense to represent and handle the first type of absence separately from the second type.
-- Table definition for employee
name, surname, date_entry , date_exit
Other than soothsaying, my choices here are: NULL, some magic value, or an additional field telling me whether the application should evaluate the field.
The latter just makes the code more complicated.
Magic values may not be portable, and lead to a whole range of other problems if applications forget to check for them; I count myself lucky if the result is something as harmless as an auto-email congratulating an employee to his 7000s-something birthday.
That leaves me with NULL. Yes, it causes problems. Many problems. But at least it causes predictable problems like apps crashing when some python code tries to shove a NULL into datetime.datetime.
So for Postgres it is generally true that storing NULLs is very cheap in most cases.
Postgres uses a fixed-size null bitmap and variable-size rows, so a NULL value takes one bit in the bitmap (and additional nullable columns may require a wider bitmap), but they are skipped in the row itself.
Consider a "docs" table where each doc has a unique name, under a given parent doc. A null parent would be a top-level doc, and top-level docs just have a unique name. This didn't work before, and would hopefully be addressed by PG15.
I'm not sure if null parents really represent a "problem with my data", or if the tree structure was too exotic for PG to support properly.
How I got around it: hardcode a certain root doc ID, then the parent column can be NOT NULL. But this felt janky because the app has to ensure the root node exists before it can interact with top-level docs. Plus there were edge cases introduced with the root doc.
This feels like an actual real-world thing people might want to do where indeed you'd want to have a single NULL value.
The biggest reason is it makes finding all descendants very fast with a prefix search (foo/bar/%) on the path column when it's indexed. It's not unusual to want to find or update all descendants because descendants are usually related. If you don't have a path column, then you need to write a recursive CTE, which is Slow, or recurse in your application, which is normally even slower. The reason they're slow is they require a number of seeks which is exponential in the depth of the tree.
It also makes lookup of a node from the path fast, and producing a qualified path to a node fast, but these costs are linear in path length.
Anyway, this path column is also a good place to put your unique constraint.
If you don't want to restrict names from containing the path separator, you can escape application-side. For example, if using '/' as a path separator, consider '::' to escape ':' and ':s' to escape '/' - don't use your path separator in the escape or it'll muck up prefix searches.
You can still get NULLs at query time, and they'll have semantically valid meanings in views and subqueries.
I would make the distortion columns nullable for the pratical reason that you either have to duplicate all the work for querying the same properties from the meters (for example a dashboard that shows voltages only). If you want to support both types of meters in the same dashboard you would have to do a UNION type query (and not ever forget to do that!) if you store the measurements in a different table.
I come at this with little experience with SQL, and having worked a bit on ampersand[1], a tool where you declare the structure of your data, and some invariants, and the tool will create a schema for your database, and some automatic checking to ensure your invariants are upheld.
Alternatively, a key / value table, e.g. `meter_id, property, value`. But that isn't very optimal, works better for things like a shop product with many different properties per product.
Edit: Since it's a date, there are valid use cases for nullable timestamps. But only if there is no additional information attached to the event you're saving in the timestamp. Another complication with nullable timestamps is sorting by them, which you often do with timestamps. With nullable ones this can get messy.
In a simplistic view maybe NULL is really the correct choice; but is it? Does NULL represent the desired properties?
Another common pattern is the use of some sort of sentinel value, E.G. the largest possible value for a date field, or the smallest, might be used to indicate an unknown maximum or minimum that propagates.
A related pattern might be some sort of orders_suppliers table which would have a foreign key value; that might be NULL or it could use a sentinel value with a dummy supplier to indicate a special condition and an arbitrary number of inband subsets which can be their own distinct matches.
A better relationship would be if the order_suppliers table was the one with a relation to this table and there wasn't even a column.
Would you suggest that a database of appearance information about a person should have a separate subtable for "hair" to properly model this feature?
Either way, in the end, for displaying 99% of the time you're going to be using a VIEW that LEFT JOINs all these cute normalized tables back together again, and then you're going to want to filter those views dynamically on a nice grid that the user is viewing, and the fact that X != X is going to bite you in the ass all over again.
Creating more tables is just moving the problem around, not solving it.
https://www.wired.com/story/null-license-plate-landed-one-ha...
That sql null is valid doesn’t make its behaviour of not being equal to itself any less weird, because it works that way essentially nowhere else, and thus is highly unintuitive to developers who don’t live and breathe sql.
The closest thing in most langages is nan and developers also find nan weird.
I'd argue the closest thing in most languages is the null propagation operator (usually using a ? symbol) that at least JavaScript and C# have.
YMMV, but personally the unique index behaviour was the main thing I found weird about NULLs. So now that I can turn that off I'm pretty happy with them.
In what sense? null-coalescing operators don’t change how nulls behave or relate to one another, they only provide convenience operators for conditioning operation upon them, like sql’s COALESCE.
I find it quite useful to know when something is null though I have a hard time explaining it.
> It's perfectly valid.
:o You'll get jumped on, saying this to a forum of software people. Lots of things that are valid are also weird.
[0] https://www.postgresql.org/docs/14/functions-logical.html
Say you have a unique constraint on two columns (column_a, column_b). column_a is not nullable, column_b IS nullable. Obviously these values are unique:
(1, 2) (1, 3) (2, 2)
But these values ARE ALSO UNIQUE:
(1, NULL) (1, NULL)
It is obvious in hindsight (NULL isn't equal to NULL), but can be quite a surprise.
...
Oh wait, postgres 15 deals with just this situation. Huh.