Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Nullable but not null

efe.me

Post reply on HN
Print view View original

41–49 of 49 posts

Re: Nullable but not null

#41

Post by tudorg »

avg_dev wrote: post #34
tudorg wrote: post #29

Earlier quoted context omitted.

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.

Thanks for the info. One minor point: > But `timeofday()` is not volatile, so `DEFAULT timeofday()` is going to lock the table for a long time. Perhaps the “not” was a typo?

Ah yes, it was a typo, sorry.

Re: Nullable but not null

#42

Post by Lvl999Noob »

nlitened wrote: post #39
stux wrote: post #22

This 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…

> you need a concept of absence that's different from null Could you give an example? Null _is_ absence, the way I see it

What they are saying is that the field is always present in the domain model but we don't have the information to backfill it. For example, say you have a customers table. Originally, it just stored their name and internal ID. But now you are adding in their government ID as well. Except that you already have thousands of customers and you don't have their government ID. So you either make the column nullable and slowly backfill it over time. Or you find some default value which isn't null but the code understands it to still be empty. And again, you slowly backfill over time.

Re: Nullable but not null

#43

Post by Lvl999Noob »

codingdave wrote: post #4

How would the database know whether the other app layers depend on that value or not? You could absolutely have an app that does not require data in a specific field to function, yet all records happen to have data. This is actually fairly common in single-tenant apps, where some tenants populate a field and others do not. You need to look at how the data is used across the entire stack to know whether or not it shou…

The script example in TFA is just a starting point. I believe you would still manually go through all the columns it finds and decide which ones are actually supposed to be nullable and which ones don't need to be. As the article said, nullable fields that don't contain null could be a sign of incomplete migrations and such.

Re: Nullable but not null

#44

Post by nlitened »

Lvl999Noob wrote: post #42
nlitened wrote: post #39

Earlier quoted context omitted.

> you need a concept of absence that's different from null Could you give an example? Null _is_ absence, the way I see it

What they are saying is that the field is always present in the domain model but we don't have the information to backfill it. For example, say you have a customers table. Originally, it just stored their name and internal ID. But now you are adding in their government ID as well. Except that you already have thousands of customers and you don't have their government ID. So you either make the column nullable and slo…

> So you either make the column nullable and slowly backfill it over time. Or you find some default value which isn't null but the code understands it to still be empty. And again, you slowly backfill over time.

What would be the justification for using some other “default value” in this case? That’s just null with extra steps. Null _is_ the default value

There’s nothing gross or unholy about null values. Very smart people many years ago envisioned them as part of relational databases specifically for the use cases like these.

Re: Nullable but not null

#45

Post by mort96 »

nlitened wrote: post #44
Lvl999Noob wrote: post #42

Earlier quoted context omitted.

What they are saying is that the field is always present in the domain model but we don't have the information to backfill it. For example, say you have a customers table. Originally, it just stored their name and internal ID. But now you are adding in their government ID as well. Except that you already have thousands of customers and you don't have their government ID. So you either make the column nullable and slo…

> So you either make the column nullable and slowly backfill it over time. Or you find some default value which isn't null but the code understands it to still be empty. And again, you slowly backfill over time. What would be the justification for using some other “default value” in this case? That’s just null with extra steps. Null _is_ the default value There’s nothing gross or unholy about null values. Very smart…

I want to represent that a field should never get new null values, it should be treated as non-nullable for the purpose of writing; however there are historical records which were made before the introduction of the field, so it should be treated as nullable for the purpose of reading.

Re: Nullable but not null

#46

Post by whatevaa »

nlitened wrote: post #44
Lvl999Noob wrote: post #42

Earlier quoted context omitted.

What they are saying is that the field is always present in the domain model but we don't have the information to backfill it. For example, say you have a customers table. Originally, it just stored their name and internal ID. But now you are adding in their government ID as well. Except that you already have thousands of customers and you don't have their government ID. So you either make the column nullable and slo…

> So you either make the column nullable and slowly backfill it over time. Or you find some default value which isn't null but the code understands it to still be empty. And again, you slowly backfill over time. What would be the justification for using some other “default value” in this case? That’s just null with extra steps. Null _is_ the default value There’s nothing gross or unholy about null values. Very smart…

If null is an actual value, you need another value to represent "undefined".

Null is also considered a billion dollar mistake by very smart people.

Re: Nullable but not null

#47

Post by whatevaa »

lock1 wrote: post #20
deepsun wrote: post #16

I'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.

You end up with tons of useless error handling code which can only "halt and catch fire" because there is no recovery from a lot of those cases.

Re: Nullable but not null

#48

Post by bazoom42 »

whatevaa wrote: post #46
nlitened wrote: post #44

Earlier quoted context omitted.

> So you either make the column nullable and slowly backfill it over time. Or you find some default value which isn't null but the code understands it to still be empty. And again, you slowly backfill over time. What would be the justification for using some other “default value” in this case? That’s just null with extra steps. Null _is_ the default value There’s nothing gross or unholy about null values. Very smart…

If null is an actual value, you need another value to represent "undefined". Null is also considered a billion dollar mistake by very smart people.

If you are referring to Tony Hoares talk, it is not nulls which is the billion dollar mistake. The mistake is type systems which does not distinguish between nullable and non-nullable.

Re: Nullable but not null

#49

Post by nlitened »

mort96 wrote: post #45
nlitened wrote: post #44

Earlier quoted context omitted.

> So you either make the column nullable and slowly backfill it over time. Or you find some default value which isn't null but the code understands it to still be empty. And again, you slowly backfill over time. What would be the justification for using some other “default value” in this case? That’s just null with extra steps. Null _is_ the default value There’s nothing gross or unholy about null values. Very smart…

I want to represent that a field should never get new null values, it should be treated as non-nullable for the purpose of writing; however there are historical records which were made before the introduction of the field, so it should be treated as nullable for the purpose of reading.

> I want to represent that a field should never get new null values, it should be treated as non-nullable for the purpose of writing

That's a job of DB triggers — arbitrary restrictions that are automatically applied when you create or update records. You can restrict new null values, you can restrict numeric values to be odd or prime, you can make sure that only a single field can be null out of two, but not two at the same time — all the things that you can't even describe in a type system.

Post reply on HN