6 ms·
> select null = null; returns NULL, because each NULL is basically a placeholder representing any “unknown value”. Two unknown values are not necessarily the sa
by demurgos 2y ago
> select null = null; returns NULL, because each NULL is basically a placeholder representing any “unknown value”. Two unknown values are not necessarily the same value; we can’t say that they are equal, because we don’t know the value of either of them.
Agreed with all of this, it would probably have been better if they were named `unknown` instead of reusing the `null` keyword.
Note also that since Postgresql 15, you can use `NULLS NOT DISTINCT` when creating a unique index [0]. I'm less familiar with other databases.
0: https://www.postgresql.org/docs/15/sql-createtable.html https://www.postgresql.org/docs/15/sql-createtable.html
- NoMoreNicksLeft 2y ago>also that since Postgresql 15, you can use `NULLS NOT DISTINCT` when creating a unique index [0]. I'm less familiar with other databases. Why would anyone want to use another database?
- stronglikedan 2y agoSimplicity. PG is often overkill for simple apps, where MySQL/Maria/et al is easier to maintain, or even SQLite for very simple apps where zero DB maintenance is preferable.
- lcnPylGDnU4H9OF 2y agoWhy would you say MySQL/Maria/et al are easier to maintain for simple apps than PG?
- demurgos 2y agoThe main pain point for smaller apps is that every major Postgres version requires an explicit migration of the underlying data representation. I get why it's there, but for simpler apps I would appreciate a flag to do it transparently.
- throwaway10235 2y agoI'm not sure what you mean. I have migrated versions without having to update any applications that connects to it? Maybe it is a driver specific issue? I have used Python/Java, and haven't updated any of my code or dependencies because of a major Postgre update
- homebrewer 2y agoIt requires manual interventions because the upgrade process is basically dump + restore. MySQL and MariaDB upgrade between major versions automatically — you simply install the next version (or change the version tag in your container definition) and restart the server. Usually it takes almost no time, altought might be just as slow as PG when major changes to the data format are introduced. The only example I can remember is 8.0 when oracle completely rewrote the data format (making things like atomic ddl possible).
- panzi 2y agoYou need to dump the database on the old PostgreSQL version and then load the dump on the new PostgreSQL version. Some other database servers can just use the old table data or migrate the data files on the fly transparently.
- Volundr 2y agoIt's not client side, it's server side. The binary format between Postgres major versions is generally not compatible so you need to do a pg_dump from the old database and do a pg_restore into the new one. pg_upgrade [1] can let you bypass this by migrating the binary data directly, but still requires having both the new and old postgres versions installed. There's also things you can do with replication, but since we're talking simplicity I don't think that really applies. Personally I think upgrades are the one thing MySQL has on Postgres at this point. [1] https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html
- marcosdumay 2y agoThe GP is complaining about the need to run the upgrade cluster command, and reconfigure your server when you want to use a different version of postgres itself. MySql does it transparently if you just open the database in the new server. Of course, lots of people also think it's a feature. But those aren't very vocal. Anyway, this is a main reason why people keep using old postgres versions, they have to think about upgrading, and they often don't.
- phplovesong 2y agoUsually its a skill issue
- badlibrarian 2y agoVACUUM
- kstrauser 2y agoIf your data's large and changing enough that you have to care about vacuuming, any reasonable database is going to require some tuning, tending and management. I'd posit that only a tiny fraction of PostgreSQL uses have to know or care that vacuuming is a thing because the autovacuum default handle it for them.
- mardifoufs 2y agoSure, it's never going to be plug and play, but it doesn't mean that all the issues will be equivalent. Vacuuming doesn't really have an equivalent in say, MySQL. It's something you don't have to worry about if you use the latter. For example, HA and clustering will always be challenging to deploy/maintain, but you will still have a harder time doing that with postgres than with MySQL. Postgres also has a lot of benefits obviously, though.
- deleted 2y ago[deleted]
- homebrewer 2y agoAlso the reverse — MySQL et al support much more complex replication topologies out of the box, including multi master for the very rare use case when you need it. It's also much easier to tune, most database instances require setting innodb_buffer_pool_size, and that's basically it. Newer versions can even set it automatically if you're fine with consuming all memory on that machine, thus requiring no tuning at all.
- password4321 2y agoReplication
- poincaredisk 2y ago* legacy applications or vendor lock-in * use of a cloud provider that favours another database (like SQL server) * some people claim mysql/maria is faster for them, simpler, or has a better support for replication * use of sqlite for lightweight or portable apps * domain specific databases that still use sql as their query language * someone may want to use another database for fun or to learn something new
- hot_gril 2y agoSQLite has its own use cases. And MySQL was all-around better than Postgres in the past, so it still has more usage in some areas. Nowadays I'll take Postgres over MySQL by default, but it's not a huge difference.
- duncan-donuts 2y agoIntroducing “unknown” feels like another kind of hell like undefined in JavaScript.
- demurgos 2y agoJust to clarify, I'm not advocating to introduce a new `unknown` keyword. I'm saying that the existing `null` in SQL was not named properly and that the name `unknown` would have been more fitting. SQL's `null` already has the semantics of `unknown` as explained in the part of the article that I quoted.
- wvenable 2y agoSQL's use of "null" is probably one of the oldest instances of that concept in computing. It's exactly equivalent to unknown. That is its definition.
- demurgos 2y agoReally? I know that SQL is old but I would have expected `null` to refer to pointers at first. Going by Wikipedia, I see that SQL is from 1974 and C from 1972. Were there earlier uses/drafts where `null` is "unknown" instead of "unset"?
- wvenable 2y agoI wouldn't necessarily define `null` as "unknown" -- it's just "no value" -- which is really the same thing and also somewhat equivalent to "unset". But null pointers aren't unset as pointers aren't initialized to null in C and you can explicitly set a pointer to null. E.F. Codd added nulls to relational model in 1970 so that does pre-date C. The concept is even older than that I imagine.
- recursive 2y agoIn nth normal form, you can't have 'no value'. That would mean your model is wrong. In academic relational data books, null does mean "unknown". There is a value, we just don't know what it is (yet). If there might actually not be such a value, you're supposed to change your schema to reflect that.
- magicalhippo 2y agoThe result of comparisons involving NULL values can result[1][2] in UNKNOWN, and in PostgreSQL for example you can test[3] for this using IS UNKNOWN. That said, as someone self-taught in SQL, I agree NULL was not a good choice. Replacing NULL with UNKNOWN and the third boolean value as INDETERMINATE for example would have been better. [1]: https://stackoverflow.com/a/79270181 https://stackoverflow.com/a/79270181 [2]: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/null-and-unknown-transact-sql https://learn.microsoft.com/en-us/sql/t-sql/language-element... [3]: https://www.postgresql.org/docs/current/functions-comparison.html https://www.postgresql.org/docs/current/functions-comparison...
- otteromkram 2y agoAlso self-taught SQLer and I don't have an issue with NULL. I also don't use UNIQUE constraints, so maybe that has something to do with it.
- deleted 2y ago[deleted]
- magicalhippo 2y agoI don't have an issue as such, I was a fairly experienced developer first time I had to dabble with SQL, but sometimes it can still surprise. For example I learned the hard way that the DB we use at work does not index NULL values. And once in a while if I'm tired or stressed I might forget about UNKNOWN and thus that "Col <> 42" does not return rows where Col is NULL. Not that better naming would prevent such surprises, but I still think the current naming is less than optimal from a pedagogical perspective. At least I see this at times when teaching our support folks SQL (many have domain background and not a technical background).
- labster 2y agoSQL was developed in the 1970s, there’s no way they’d waste all those bytes to spell out UNKNOWN and INDETERMINATE.
- 2y ago
- layer8 2y agoSQL NULL doesn’t behave like “unknown” in all contexts. That’s one issue of NULL, that it doesn’t really have consistent semantics.
- masklinn 2y agoFurthermore if null only means unknown then we need a value for “known absent”, there’s a reason why null is so often used as that.
- thaumasiotes 2y agoDo you actually need that in a Boolean context? It would only be useful for evaluating self-referent claims like "this sentence is false".
- masklinn 2y agoYour questions might be relevant if null were limited to boolean contexts. It’s not.
- int_19h 2y agoFrom a purely relational perspective, if some piece of data can be absent, it's a 1:N relation where N<=1, and ought to be encoded as such. (Of course, this is rather awkward in practice, and when NULL is there, it's inevitably going to be used for that instead.)
- feoren 2y agoIt is encoded as such. That's why most columns are made nullable. It's crazy to say you need to use the full power of a 1:N relation with some child table when you know N cannot be greater than 1, when a nullable column already exactly encodes a 1:(0..1) relation. I'm not trying to shill for null here: one of null's great problems is exactly the fact that null can represent "unknown", "known absent", "not applicable", or even some sentinel-ish "other" escape condition, each with their own subtle differences in how they should be handled. Null has tons of problems, of course. But it's patently absurd to claim that you "ought to be" making a different 1-column table with a unique foreign key or shared primary key for every column that might be absent, because of some vague appeal to the fact that you can write it as a 1:N relation with N<=1. You can just as easily claim that every non-nullable column is a 1:N relation where N==1 and "should be encoded as such". It is encoded as such! That's what a column is!
- cm2187 2y agoThe problem is that in practice in a database NULL is a placeholder for a missing value, not an unknown value.
- bballer 2y agoAnd to further apply semantics to this just to be snide, the value is only "missing" if it could possibly ever be defined for that tuple. There are cases where you expect the value to be "missing", and it not being "missing" would be considered a data integrity issue. Fun.
- cm2187 2y agoYes I should have rather written “an absence of data”. But still not “unknown”.
- SoftTalker 2y agoBest way to think of NULL is "no value." Not "unknown value," as that implies that it is a value you just don't know what it is. Not "missing" value as that even the notion of a value being "missing" tells you something. NULL is no value. It's like a black hole, it consumes anything it touches. Any expression involving a NULL term becomes NULL.