That would be horrible if you had other systems that predated and did depend on the null (you could argue they shouldn't do that but well shouldn't and didn't aren't the same thing alas).
Why is NULL a bad thing? Sometimes I absolutely want to know if a field is NULL, so i can coalesce to the desired value. I know this is SQL Server specific, since Oracle treats empty strings and NULLs the same, but that approach has always felt wrong to me. Thoughts?
Edit: I see maybe that I should have scrolled down further before posting this. Lots of discussion further down about NULL values.
In my experience most of the time a field is nullable it’s by accident. Some RDBMS and/or SQL “IDEs” default to allowing null too, which doesn’t help matters. Personally I think NULL shouldn’t be supported unless the developer categorically sets a “yes, I actually do want this archaic option enabled” flag in their DB backend.
Edit: getting a lot of heat for this comment from people misunderstanding it. I’m not saying “null” shouldn’t exist, just that it shouldn’t be the default. Side point: most of the time people think they need null they don’t.
“Unset” is an extremely valuable state to be able to check. It’s why we have null/nil in well-designed languages. The zero value of a golang int is not the same as indicating a lack of presence. There are plenty of scenarios where “unset” is a necessary state vs the zero value.
I agree however it is only valuable under specific circumstances while it can frequently create hidden traps. On balance it causes more problems than it solves. Which is why I suggested it should be an optional flag enabled on the backend rather than the default.
Like everything, it depends on your language and framework. But after 30 years of experience writing software in strictly typed languages, I’ve seen lots of people get caught out with nullable fields.
However if you’re main body of experience is Perl (for example) then NULL isn’t ever an issue.
I get Perl isn’t trendy these days but Perl actually has a very robust set of DB libraries and I have worked in several places were it was the preferred language.
Perl has by far the best DB tooling of any of the many languages I’ve worked with. My point was sarcasm as rebuttal to the idea that a Perl developer might not have encountered a null value
Actually I said NULL isn’t an issue in some languages with looser type systems, not that other developers wouldn’t have encountered a NULL before.
Eg Perl doesn’t have a nil type like Go, so variables passed to DBI which are NULL in the returned SQL query will be undef’ed instead. Effectively a bit like NULL but it only causes a problem if your running with strict flags (which you shouldn’t do on production systems anyway for various reasons; but that’s another topic entirely) otherwise that value falls back to the language default of “”/0/false.
With languages like Go you can easily end up with a panic if you’re not careful (where you’re trying to cast a nil interface{}). Which, I’m guessing, is why this Go library being discussed here does it’s own NULL checking.
It’s different because the DBI wouldn’t return an object for a nullable table record. Which is what we are specifically talking about. Sure other parts of your code might fail if you’re trying to use a scalar as an object (or such like) but that would consistently fail regardless of nullable types on the database.
So I’m not going to argue that Perl doesn’t also have its own hidden traps (a great many of them in fact) nor that it’s interpretation of OOP isn’t terrible but the scope of this discussion was about nullable types and not anything else you now want to draw into the argument.
I’m sure you’d love to consider yourself infallible but human error can happen even to the best of us.
However if we’re talking from idealistic perspective then you’d have your own DBAs who would create the DB and write the SQL so your developers just call your (for example) stored procedures like APIs rather than writing their own - often unoptimised - SQL themselves.
Nullable is being set by accident? Nullable values are an “archaic” option?
Nullable is different than empty. Consider a contrived example where I’ve extended a user table to include a new column “fullName”. By making this a nullable column, I can determine that a user has not set this value. But maybe the user sets it to an empty string. The API might want to know this and handle it differently — prompting for a value if it’s null, but ignoring “” as “this is what the user wanted to set”.
Without nullable values, I would have to create bit values in a lot of tables indicating if the value was ever set.
Another example is time stamps. Databases have timestamps for createdAt and updatedAt often. There are people who set updatedAt when they set createdAt, but there are people who don’t. I’m in the later camp. If an object was created but never updated, I want to be able to know that. How would I do this if the updatedAt column is not nullable? I’d either have to set a bit field that it has been updated, which feels suboptimal, or I could set it to something like “time.Empty{}”, which is a magic number tied to the programming language? There are plenty of other solutions here, sure, but what’s wrong with nullable fields?
First example: why would you want to handle null and a zero length string differently in that case? That seems a little contrived to me because in both instances the user has elected not to enter that information.
Re your timestamp example, just compare if created is the same as updated. That’s a really easy problem to solve. Though I have seen other scenarios when a timestamp needed to by nullable so I agree there will be edge cases.
I agree that null does have its uses (it’s basically essential for outer joins) but most of the time people think they need null they don’t actually need it. Plus it frequently it causes hidden faults, so it’s usually best to avoid allowing nullable fields unless you’re sure there isn’t any other easy way.
The tool referenced here is for go, a programming language that has pointers. Pointers can be nil too, which is useful for many of the same reasons that null database columns are useful. There is a difference between uninitialized, initialized with default values, and a custom value.
You may not have this use case you in your application, but I do. I have real scenarios that need to know if a value is initialized or not. I agree that I could solve this in other ways, but nullable columns just aren’t an archaic database option just because you can implement the same behavior in the application code or by rewriting the SQL query.
I’ve had a great deal of experience with Go and there are many who’d argue that nil pointers in there is a mistake as well (though I’ve personally never had an issue - but I do write pretty robust tests too).
I have ran into some instances when I have needed nullable fields in relational databases as well. I’m not claiming null shouldn’t exist. My point was that they should be an opt in rather than opt out because they cause more problems than generally they solve.
Regarding timestamp, I often have a soft delete timestamp 'deletedAt' in which it must have a null default value. I think this is a perfectly acceptable usecase of db null values
It is though I’d be more tempted to have that as it’s own table with more data attached such as the UID who deleted it, reason (if application supported that), etc. That way you can have more detail on “deleted” records without having to null several fields nor increase your table size for your main table with lots of “deleted” metadata when typically records wouldn’t be marked as “deleted”.
Though “deleted” isn’t really the right term here either because you’re not really deleting data, just removing it from view.
Edit: we've given you many warnings. Continuing will eventually get your account banned, so would you please take the spirit of HN more to heart and fix this? We'd be grateful.
Firstly, I’ve never said people shouldn’t have NULL. I said columns should default to be nullable.
Secondly, I’d already given outer joins as an example of why SQL needs to support NULL.
I would be nice that people like yourself bothered to read the comments your reacting to instead of jumping off the deep end (-4 on my original post now and people like yourself are literally just reiterating the same points I’ve been making. It’s insane)
If five people misunderstood your comment (which says nothing about outer joins as I reread it) maybe it is your writing that would need to be clearer.
Maybe but my experience on HN is that there’s sometimes a mentality of “fools never differ” on here. I’ve seen countless correct and/or experienced commenters get downvoted even when the comment is well written.
It’s also worth remembering that five people is still only a very small subset of the hundreds that would have read the comment and did understand/agree but didn’t bother to comment for fear of the wrath of those who have already expressed disagreement. For example I was actually voted up several times before the others commented (as well as a few times since).
So it’s really not worth blaming individuals for the comments of others. The only possible outcome from that is starting another, much stupider, argument.
Yeah, the world would probably be a better place if the default in a column definition was NOT NULL rather than NULL.
That's not really relevant to this discussion, though -- once you do have a nullable column in your schema, it doesn't make sense in the Go layer to pretend you don't.
I agree that NOT NULL should be the default for columns, unfortunately that ship has sailed.
Assuming that the nullable foreign key problem could be solved, I think that NULL is valuable enough (and not that rare) that it ought to be the database owner's choice whether to allow them or not, as opposed to the superuser (not sure which way you're suggesting).
> I agree that NOT NULL should be the default for columns, unfortunately that ship has sailed.
Not really. Defaults change all the time. I’m not asking for a feature to be removed after all.
I don’t deny the change in any specific RDBMS would have to be carefully considered but I’ve worked on a lot harder projects to make transitions in default behaviours than this would be so it’s certainly doable if the momentum was there. However I doubt enough people care enough (if this thread is anything to go by).
> Assuming that the nullable foreign key problem could be solved
Offhand I can think of a few ways to solve that but I also think it’s one of the instances where NULL makes the most sense
> I think that NULL is valuable enough (and not that rare) that it ought to be the database owner's choice whether to allow them or not, as opposed to the superuser (not sure which way you're suggesting).
I completely agree the DBA should still have the power to enable it. My point was just that columns shouldn’t default to being nullable during a CREATE.
To be honest, this could just as easily be “fixed” in graphical database management tools. That would at least enable seasoned DBAs to utilise NULL while preventing others from some of the pitfalls that nullable fields can introduce in some languages. However I still stand by my point that it’s not really a sane default for the way most people architect databases these days (or even “should” be architecting).
Maybe this is true? I may not have thought through the entire feature that carefully.
The initial thought behind it: Go programmers generally use values for scalars instead of pointers. Can we make an sql mapper that reflects that general practice?
Java JDBC use similar approach for primitive types and has method wasNull which returns true if the last retrieved value was actually null. I hate that approach, honestly, but that's from Java perspective, may be it's more natural for Go developers.
There's an inherent semantic mismatch here. SQL scalars have a dimension (NULL) that Go scalars don't have, so Go scalars just don't have enough information to represent SQL values. You have to put that dimension somewhere. Rather than using Go's separate dimension of nil-ness of pointers, you could put it in a separate boolean return value, or wrap it in a struct (see C#'s Nullable<T>), or make it queryable with a separate method (like IsNull(index)). Whatever it is, it also has to be two-way, so you can write NULLs as well as read them.
For any practical usage, this is a fundamentally broken implementation.
I spend something like 10% of my working hours un-fucking data that was poisoned by lazy, inconsistent, and/or incorrect handling of missing data.
This is what makes Excel such a dumpster fire for handling data. It is absolutely the wrong decision to make any new piece of software that handles missing data like this.
Go's defaults for types are great in a lot of go specific ways, but anyone who has dealt with needing to use pointers all over the place in DTOs knows how disgusting it can get.
You can also work together with who pick me out of collections and debt, He's so kind, real, legit and with affordable price. he help me boost my credit score to 795 plus pay off my credit card and business loan debt within the next 4 days after i contact him and pay the upfront payment he ask for. Get him for similar issue on : richskrentacyberservice AT gmail DOT com He was the one who made me happy again.
It was so so nice that kevin introduce me to Permanentcreditrepairer the most creditable and reliable hacker on internet and no one has ever spoke nagetive about him. he`s into various kind of jobs like : Facebook hacking/spying, Whatsapp spying, Phone tracking and cloning, Snapchat, Instagram, Wechat, Text messages, Bank statement, Increasing your credit score, Hack hangouts or any media messenger or sites. You can contact him via PERMANENTCREDITREPAIRER AT gmail DOT com. YOU WILL BE GLAD YOU DO SO..... GOODLUCK!!!
I was in such a hurry because I needed my credit score boost up quickly for the personal loan i need to enlargement family. Have been powerless for years my kids doesn’t go to a school of my choice. Thanks to Rich who light up the candle, He Erase My Negative Credit Bureau, Equifax off my TransUnion and get up my score to 797 along the process. Reach him via # plus mail: richskrentacyberservice@gmail.com. +1 (440) 459-0850
Hello friends i just have to share this very incredible hacker who you will enjoy is service so much that you would be unable to stopnworking with him, I had a low credit score and late payments. so i got strongly attracted with Rich Skrenta HACKER who increased my credit score to 810 across 2 credit bureaus within 2weeks and do away with the late payment plus car loans. i never appreciate this stuff was real until i experienced it myself. Info to contact: RICHSKRENTACYBERSERVICE AT GMAIL DOT COM or by use of (440)459-0850
Note! if you are urgently in need of a personal loan or you need a hacker to repair your credit score and remove all negative Equifax. Here is the light to the solution: PERMANENTCREDITREPAIRER@GMAIL.COM. I saw some testimonial shared about him on an article about how people says good things about him, he had save many lives and he deliver his services with all sincerity. I have also tried him and i didn't regret! He help me apply for a loan of $70,000.00 and also pay off my bank dept along the line.
54 comments
[ 3.2 ms ] story [ 115 ms ] threadThis just seems...wrong. Why would the column be nullable if you don’t actually care if it’s NULL?
if I read out a nullable text field and write the same struct back into the database is it going to insert an empty string into that column?
Also related, how would auto incr pk work?
You should use UUIDs only when, you know, you need a UUID.
Edit: getting a lot of heat for this comment from people misunderstanding it. I’m not saying “null” shouldn’t exist, just that it shouldn’t be the default. Side point: most of the time people think they need null they don’t.
NULL is what we've got though. setting columns to not null is leaps ahead of most programming languages anyway.
This is far from clear
However if you’re main body of experience is Perl (for example) then NULL isn’t ever an issue.
Eg Perl doesn’t have a nil type like Go, so variables passed to DBI which are NULL in the returned SQL query will be undef’ed instead. Effectively a bit like NULL but it only causes a problem if your running with strict flags (which you shouldn’t do on production systems anyway for various reasons; but that’s another topic entirely) otherwise that value falls back to the language default of “”/0/false.
With languages like Go you can easily end up with a panic if you’re not careful (where you’re trying to cast a nil interface{}). Which, I’m guessing, is why this Go library being discussed here does it’s own NULL checking.
How is this any different to the runtime error you’d get in Perl if you attempted a method call on an undefined value you’d expected to be an object?
So I’m not going to argue that Perl doesn’t also have its own hidden traps (a great many of them in fact) nor that it’s interpretation of OOP isn’t terrible but the scope of this discussion was about nullable types and not anything else you now want to draw into the argument.
That would be Perl 5. Perl 6 does. From https://docs.perl6.org/type/Nil :
"The value Nil may be used to fill a spot where a value would normally go, and in so doing, explicitly indicate that no value is present."
However if we’re talking from idealistic perspective then you’d have your own DBAs who would create the DB and write the SQL so your developers just call your (for example) stored procedures like APIs rather than writing their own - often unoptimised - SQL themselves.
Nullable is different than empty. Consider a contrived example where I’ve extended a user table to include a new column “fullName”. By making this a nullable column, I can determine that a user has not set this value. But maybe the user sets it to an empty string. The API might want to know this and handle it differently — prompting for a value if it’s null, but ignoring “” as “this is what the user wanted to set”.
Without nullable values, I would have to create bit values in a lot of tables indicating if the value was ever set.
Another example is time stamps. Databases have timestamps for createdAt and updatedAt often. There are people who set updatedAt when they set createdAt, but there are people who don’t. I’m in the later camp. If an object was created but never updated, I want to be able to know that. How would I do this if the updatedAt column is not nullable? I’d either have to set a bit field that it has been updated, which feels suboptimal, or I could set it to something like “time.Empty{}”, which is a magic number tied to the programming language? There are plenty of other solutions here, sure, but what’s wrong with nullable fields?
Re your timestamp example, just compare if created is the same as updated. That’s a really easy problem to solve. Though I have seen other scenarios when a timestamp needed to by nullable so I agree there will be edge cases.
I agree that null does have its uses (it’s basically essential for outer joins) but most of the time people think they need null they don’t actually need it. Plus it frequently it causes hidden faults, so it’s usually best to avoid allowing nullable fields unless you’re sure there isn’t any other easy way.
You may not have this use case you in your application, but I do. I have real scenarios that need to know if a value is initialized or not. I agree that I could solve this in other ways, but nullable columns just aren’t an archaic database option just because you can implement the same behavior in the application code or by rewriting the SQL query.
I have ran into some instances when I have needed nullable fields in relational databases as well. I’m not claiming null shouldn’t exist. My point was that they should be an opt in rather than opt out because they cause more problems than generally they solve.
Though “deleted” isn’t really the right term here either because you’re not really deleting data, just removing it from view.
Then consider getting some more.
https://news.ycombinator.com/newsguidelines.html
Edit: we've given you many warnings. Continuing will eventually get your account banned, so would you please take the spirit of HN more to heart and fix this? We'd be grateful.
Secondly, I’d already given outer joins as an example of why SQL needs to support NULL.
I would be nice that people like yourself bothered to read the comments your reacting to instead of jumping off the deep end (-4 on my original post now and people like yourself are literally just reiterating the same points I’ve been making. It’s insane)
It’s also worth remembering that five people is still only a very small subset of the hundreds that would have read the comment and did understand/agree but didn’t bother to comment for fear of the wrath of those who have already expressed disagreement. For example I was actually voted up several times before the others commented (as well as a few times since).
So it’s really not worth blaming individuals for the comments of others. The only possible outcome from that is starting another, much stupider, argument.
That's not really relevant to this discussion, though -- once you do have a nullable column in your schema, it doesn't make sense in the Go layer to pretend you don't.
It’s tangential but still relevant to the discussion.
> once you do have a nullable column in your schema, it doesn't make sense in the Go layer to pretend you don't.
I agree and never suggested what this Go API was doing did make sense.
Assuming that the nullable foreign key problem could be solved, I think that NULL is valuable enough (and not that rare) that it ought to be the database owner's choice whether to allow them or not, as opposed to the superuser (not sure which way you're suggesting).
Not really. Defaults change all the time. I’m not asking for a feature to be removed after all.
I don’t deny the change in any specific RDBMS would have to be carefully considered but I’ve worked on a lot harder projects to make transitions in default behaviours than this would be so it’s certainly doable if the momentum was there. However I doubt enough people care enough (if this thread is anything to go by).
> Assuming that the nullable foreign key problem could be solved
Offhand I can think of a few ways to solve that but I also think it’s one of the instances where NULL makes the most sense
> I think that NULL is valuable enough (and not that rare) that it ought to be the database owner's choice whether to allow them or not, as opposed to the superuser (not sure which way you're suggesting).
I completely agree the DBA should still have the power to enable it. My point was just that columns shouldn’t default to being nullable during a CREATE.
To be honest, this could just as easily be “fixed” in graphical database management tools. That would at least enable seasoned DBAs to utilise NULL while preventing others from some of the pitfalls that nullable fields can introduce in some languages. However I still stand by my point that it’s not really a sane default for the way most people architect databases these days (or even “should” be architecting).
The initial thought behind it: Go programmers generally use values for scalars instead of pointers. Can we make an sql mapper that reflects that general practice?
Maybe the answer is no.
I spend something like 10% of my working hours un-fucking data that was poisoned by lazy, inconsistent, and/or incorrect handling of missing data.
This is what makes Excel such a dumpster fire for handling data. It is absolutely the wrong decision to make any new piece of software that handles missing data like this.
No frills, just a little reflection to tie struct fields <=> SQL params.
- the link to GoDoc is not working yet: https://godoc.org/github.com/colinjfw/sqlkit
- Typo in the README: Unmarsal instead of Unmarshal
Without going through everything in detail, why would I pick this over the very popular sqlx? https://github.com/jmoiron/sqlx
Short answer: It's sqlx + a query builder + some extras around transaction management.