Am I missing something here in my (MS) SQL world? if a new field is added as null, I do that to the (now) 20 year old system to we don't break 100's of stored procs - any (new) code that needs that field, has to check for it being null...
Nullable but not null
21–30 of 49 posts
Re: Nullable but not null
#22This is interesting! A field being nullable because it's legitimately optional in the domain model is one thing, but for new fields which shouldn't be nullable in the domain model, unless you can pick a reasonable identity value, you need a concept of absence that's different from null. Luckily the intersection of "non-nullable fields" and "fields with no reasonable identity value" and "fields which didnt exist in v1 of the domain model" is normally pretty small, but it's painful when it happens.
This reminds me of frozen/nonfrozen enums in Swift. You can do exhaustive case analysis on frozen enums, but case analysis on nonfrozen enums requires adding an `@unknown default` case.
https://docs.swift.org/swift-book/documentation/the-swift-pr...
Re: Nullable but not null
#23Should not we look for database to be able to do online, efficient non locking addition of column with any default value, not just NULL rather than application to have a complicated and fragile logic ?
Re: Nullable but not null
#24Should not we look for database to be able to do online, efficient non locking addition of column with any default value, not just NULL rather than application to have a complicated and fragile logic ?
I believe PostgreSQL does this since v11, which was released in 2018: (current is v17)
> Many other useful performance improvements, including the ability to avoid a table rewrite for ALTER TABLE ... ADD COLUMN with a non-null column default
https://www.postgresql.org/docs/release/11.0/
I think there is some restriction there, like the default can't be "volatile" - I can't remember the precise definition here but I think current_timestamp would be volatile, but any static value would not.
Re: Nullable but not null
#25A column that is nullable but never null might indicate that it should be non-nullable but does not necessarily imply so. Say you have an optional comment field, it might just happen by accident that a comment was recorded for each row, but that of course becomes increasingly unlikely with each additional row in the table. There is probably no harm in checking your database for such columns, especially in tables with…
> There is probably no harm in checking your database for such columns The harm is the same as any other unreliable linter rule. Each one such rule is almost harmless. And on most places that use that kind of rule, they are extremely harmful.
With that I agree, once you start to treat the output of any linter as the truth, you are actively lowering the quality of your codebase. At the very least if it has reasonably good quality, if it is in bad shape, blindly following linters might still increase code quality, you will just not reach the peak.
Re: Nullable but not null
#26I'm glad to see people discussing [zero,infinitesimals,false,emptystring,isnull,unpopulated,missing] as if each one is a thing. They've always been things! We've just been buried in compromises and shortcuts all these years.
There should also be a [the-asteroid-has-hit-y'all-are-so-stupid] and global data systems should just just pass that around after impact until the power goes out for good.
Re: Nullable but not null
#27I've seen worse. Some teams use JSON for their data. Not only each field can be missing (aka NULL), it can also be "null". Or a different type. I envy your team who's only mistake is to forget setting NULLABLE. Rainbows and unicorns ;)
What if almost everything is NULLABLE? including the supposedly primary key and foreign keys of the table? I've had the firsthand experience building a consumer for that kind of DB and it's hell to get anything running correctly without literally writing a dedicated layer to sanity check everything.
Sounds like a bad domain, and/or poor design.
Re: Nullable but not null
#28I've never seen step 5 happen...
Re: Nullable but not null
#29Should not we look for database to be able to do online, efficient non locking addition of column with any default value, not just NULL rather than application to have a complicated and fragile logic ?
I believe PostgreSQL does this since v11, which was released in 2018: (current is v17) > Many other useful performance improvements, including the ability to avoid a table rewrite for ALTER TABLE ... ADD COLUMN with a non-null column default https://www.postgresql.org/docs/release/11.0/ I think there is some restriction there, like the default can't be "volatile" - I can't remember the precise definition here but I t…
That is correct, for non-volatile default values Postgres is quick, which means that it is generally a safe operation.
Also interesting, `now()` is non-volatile because it's defined as "start of the transaction". So if you add a column with `DEFAULT now()` all rows will get the same value. But `timeofday()` is not volatile, so `DEFAULT timeofday()` is going to lock the table for a long time. A bit of a subtle gotcha.
Re: Nullable but not null
#30For a tooling solution for this problem, and many others, pgroll (https://github.com/xataio/pgroll) automates the steps from the blog post in a single higher-level operation. It can do things like adding a hidden column, backfill it with data, then adds the constraint, and only then expose it in the new schema.