46 ms·

Postgres 15 improves UNIQUE and NULL

428 points by Twisell 4y ago

silvestrov 4y ago
> This fits with my mental model of how I think unique constraints should work with null values. Agree, this should be the default for unique indexes. Why would you ever want multiple NULLs in an unique index?
- mhaberl 4y ago
  > Why would you ever want multiple NULLs in an unique index? You would maybe want some value to be unique if it exists.
- daveoc64 4y ago
  Some kind of secondary identifier, but only for records where it is applicable (e.g. loyalty card number).
- phoe-krk 4y ago
  The rows (1, NULL), (1, NULL), and (1, NULL) - each NULL signifies an absence of a value - these NULLs may actually be later filled into (1, 1), (1, 2), and (1, 3). That's why may not know if any two NULL values are equal to one another. In some cases the above is actually important, whereas in others you may want to treat NULL as a "value" instead.
- larsnystrom 4y ago
  Multiple NULLs in a unique index are useful when an entity has 0 or 1 of a simple scalar value. For example, you might have a column with an external ID, such as a facebook login id in your users table. Not all users have a facebook id, but those who do should have a unique value in that column.
  - radiospiel 4y ago
    You might want to look at a unique partial index: "CREATE UNIQUE INDEX foo ON users(facebook_id) WHERE facebook_id IS NOT NULL". I am not sure if this really is a great modelling. If a user happens to have two logins in your table (maybe they signed up with two different emails), and then connects one of his accounts with his FB identity, what does it mean that the other entry cannot be connected to the FB identity?
- masklinn 4y ago
  Because it’s very common for attributes to be optional but unique if specified e.g. your fleet management might have a 0-1:1 relationship between employees and cars, no car can be associated with 2 employees, an employee can’t have two cars, but any number of employee can be car-less, or car employee-less (lending pool, or cars which have not been attributed yet). This also fits perfectly well with the normal interpretation / treatment of sql null as “not a value”.
  - faho 4y ago
    But an employee can't be car-less twice! That's what this change prohibits.
    - masklinn 4y ago
      > But an employee can't be car-less twice! What. > That's what this change prohibits. This change prohibits nothing, it allows specifying something (which was a bit complicated to enforce before).
      - faho 4y ago
        >This change prohibits nothing, it allows specifying something (which was a bit complicated to enforce before). It allows specifying a constraint - that you can't have two rows with the same values even if one of the values is a NULL. That's prohibiting duplicate NULLs. The change allows you to prohibit duplicate NULLs. Say you have a table (EmployeeName, CarID). You could do a UNIQUE constraint on those two attributes, but that would still allow: EmployeeName | CarID Jeff | 2 Kim | NULL Kim | NULL Here, "Kim" is car-less (NULL in the CarID field) twice, which makes no sense. Hence the new constraint.
        masklinn 4y ago
        > It allows specifying a constraint - that you can't have two rows with the same values even if one of the values is a NULL. That's prohibiting duplicate NULLs. The change allows you to prohibit duplicate NULLs. Sure. > Here, "Kim" is car-less (NULL in the CarID field) twice, which makes no sense. The relation makes no sense to start with, it didn’t need duplicate nulls for that.
- seanhunter 4y ago
  One way nullable columns are often used is like an option type. It's easy to see that you might want rows to be unique if they have Some(value) but have multiple rows where the column is None
  - almog 4y ago
    I agree that this is often the use case but depending on how the query (as well as how sparse the nulls are, the total data size and partitioning scheme I guess) it's also possible to achieve this through a join-table without introducing NULL values.
    - gsk22 4y ago
      Unless you outer join, in which case NULL values pop up out of thin air...
      - almog 4y ago
        Good point. I did try to say that whether that's a viable technique would depend on your query (but omitted some verb that should have made it better resemble a sentence in English :)). But yes, even if no nulls are present in any table, outer join could (or maybe even should) introduce them.
- goto11 4y ago
  Lets say you have a Person table with with a SSN column. The column has a unique constraint because no two people have the same SSN. But the column in nullable, since not everyone has a SSN. It would be weird to expect that only one person does not have SSN.
- jhugo 4y ago
  I can't think of a situation where I'd want "only a single NULL"; that seems much weirder. Another comment described a tree data structure where it could make sense, but I can't think of others. Nullable unique indexes usually describe some kind of optional identifier; if it's present, you want it to be unique, but it's perfectly fine for more than one row to omit it.
  - orthoxerox 4y ago
    You might want an index like this on some aggregate table. Like, shoe_sales_total, which is grouped by the id of the shop assistant that helped you with the selection. But customers that have marched straight to the till and asked for shoe polish result in records with a NULL for agent_id. With a regular unique constraint (month, agent_id), your aggregate table can end up with two records for (May-21, NULL). Of course, the workaround for such cases is simple: have a sentinel value for unassisted sales and make the column not nullable.
    - jhugo 4y ago
      Such "tables" would more typically be constructed as views (materialised if performance requires it) over underlying data. In this case the underlying data would have a row for each sale, with a normal nullable column for agent_id. The query that constructs the view would use GROUP BY, and since NULLs group together in GROUP BY it would work as you expect (grouping "no agent" sales together into a single record in the view). The database system thus handles ensuring data consistency, merely by using its features as they are designed to be used.
- knorker 4y ago
  E.g. if you have a "tax ID" column, and two people in your database don't have tax IDs because they're not <your country> tax payers. Or "payment vendor processing ID". You may have multiple entries not yet sent to the vendor, so they're NULL. This shows up literally all the time in SQL schemas.
rwmj 4y ago
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.
- nicoburns 4y ago
  Except that this change means that the behaviour of nulls is not weird anymore!
  - masklinn 4y ago
    Null is still null, sql null is always “weird” wherever it exists.
    - gigatexal 4y ago
      Why is null weird? Null means unknown. It's perfectly valid.
      - masklinn 4y ago
        “Valid” and “weird” are orthogonal concepts. 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.
        nicoburns 4y ago
        > 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.
ahmed_ds 4y ago
When something that fundamental changes what is the cascading effect? Do other functions refer UNIQUE/NULL or do they have their own versions of UNIQUE/NULL like code?
- masklinn 4y ago
  There is no fundamental change, this is an opt-in on a per-index basis. It does not change null’s identity, it only allows indexes to treat nulls as identical.
hrdwdmrbl 4y ago
Cool but this was trivially solved previously by excluding NULL from the index using a partial index. I'm more interested in problems that have no great solution, like taking the first element of a set of partitioned data. Right now you have to partition & rank, and then select the rank 1 elements in a subsequent query.
- jhugo 4y ago
  Maybe it's what you meant be "subsequent query", but you don't actually need to do it in a separate query; you can do the partition & rank in a CTE and select from the resulting table, all in a single query. It's quite tidy actually.
- masklinn 4y ago
  > Cool but this was trivially solved previously by excluding NULL from the index using a partial index. That seems like the opposite solution, treating NULLs as different in database systems which treat them as identical by default (which I think only includes MSSQL). In postgres, and most DBMS, using a partial index excluding NULL allows any number of rows to have a NULL value… which is the same behaviour as if you’re not excluding NULLs. The new opt-in behaviour is treating all nulls as the same value, and thus allowing a UNIQUE index to constraint them. This required two different partial indexes, or for constrained data subset in hacks like COALESCE-ing nulls to a sentinel.
- _flux 4y ago
  I don't think you understood what this does (or then I misunderstood this) but if you do that, then how are you going to get an index violation from having two NULL values in the index? Because that is what this allows: normally indexing NULL values permitted multiple NULL entries, but with this NULL is comparable to other NULLs, therefore you can have only one NULL value in the index. Previously I had worked this around with functional index on e.g. COALESCE(value_with_null, -1), but not always you have a good sentinel value easily available. A more complicated index (..CASE WHEN..) would solve that, but it's nice to have this directly expressible, seems efficient as well.
- silon42 4y ago
petereisentraut 4y ago
I am the author of this feature. The background here is that the SQL standard was ambiguous about which of the two ways an implementation should behave. So in the upcoming SQL:202x, this was addressed by making the behavior implementation-defined and adding this NULLS [NOT] DISTINCT option to pick the other behavior. Personally, I think that the existing PostgreSQL behavior (NULLS DISTINCT) is the "right" one, and the other option was mainly intended for compatibility with other SQL implementations. But I'm glad that people are also finding other uses for it.
- zigzag312 4y ago
  > Personally, I think that the existing PostgreSQL behavior (NULLS DISTINCT) is the "right" one, and the other option was mainly intended for compatibility with other SQL implementations. Care to explain why you think NULLS DISTINCT is the "right" default behavior? What problems does it solve to warrant additional complexity by default?
  - knorker 4y ago
    Not parent commenter, but to me it seems "obviously correct". As explained in the article NULL means "unknown". Let's say I have two people, and a "tax identifier" column. Let's say two people can't have the same tax id. Obviously "don't have one" or "unknown" fit very well in NULL, and two people can both be missing a tax id, even if those who have it need it to be unique.
    - chris_wot 4y ago
      Except that's the ambiguity of NULL in the standard. It doesn't just mean "unknown". It can also mean "no value". And you can clearly compare two elements with no value. Which is why nulls are so incredibly confusing.
      - knorker 4y ago
        Yeah that's true. All the database can say is "I don't have a value here". Not "there exists no value for this column". But this doesn't mean that two NULLs are the same. Two people with NULL tax IDs are not the same (one turns out to be unknown but existing, the other is a toddler without a tax ID). Ten people with NULL address don't live in the same house. Half declined to give address, the other half are homeless. Either way we can't send mail to them. If you use the database to have a UNIQUE constraint of "only one purchase per household" then it makes no sense to allow NULL addresses, but only allow the first NULL address purchaser to buy the thing. Or "sorry, in this hotel we only allow one guest at a time without a car, and someone else already doesn't have a car". Does that guest without a car actually have a car? I don't think that's something that the database can solve. Should databases have two separate values for unknown or no value? That sounds like a world of hurt, with two types of null.
ryanbrunner 4y ago
This is great! I have multiple places in our code where I've had to define multiple unique indexes (with conditionals) to get around this. It's definitely confusing behaviour the first time you encounter it. Honestly I think SQL already breaks it's own standard of "NULL = unknown". One example: If you do a LEFT OUTER JOIN and the joined data is missing, you get NULL in the resultset. But in that case, it's not "unknown" whether there are records that can be joined to, the answer is that there are definitively no records there. NULL is univerally considered by most programming languages to equal "not present" vs "unknown", and in many cases by SQL in practice. The ship has probably sailed on actually changing the default behaviour, but optional flags like these are a good step.
- knorker 4y ago
  NULL means "I don't have this data". It doesn't guarantee that the data doesn't exist anywhere. It's semantically ambiguous, yes. But it's not ambiguous that the database doesn't have the data. It's very clearly NULL. If "middle name" is NULL, then the database itself can't tell you if a person doesn't have a middle name, or if it's merely not yet been told the middle name. You could differentiate the two by saying empty string for "has no middle name"… except on Oracle VARCHAR2, where NULL is equal to empty string.
  - ryanbrunner 4y ago
    That's fair - as I said in another comment, it breaks down with keys. I can't think of a great way to model "this row definitively does not have whatever is referenced in another table". Using anything other than NULL for that means you can't use proper foreign keys.
  - Pxtl 4y ago
    > You could differentiate the two by saying empty string for "has no middle name"… except on Oracle VARCHAR2, where NULL is equal to empty string. This is what kills me. This seems like an obvious case where multiple flavors of NULL would make perfect sense. For example Ticket.CancellationDate isn't "unknown", it's this ticket is not cancelled. But SQL standard doesn't have a way to express "empty value" as distinct from "unknown value", so we're stuck working around the strange implications of using this square peg to fill a round hole. Oracle's bizarre "empty VARCHAR2s are NULL" is strange but is consistent with every other datatype that offers no way to say "empty" but "NULL". 0 is not the same as NULL int and is not the same as empty int. Mindate is not the same as NULL date and is not the same as empty date.
tzahifadida 4y ago
wow thanks!
pbz 4y ago
Since we have some PG devs here: Can we please have a way to reorder columns? Coming from MySQL, this is one of the first missing features one hits. It's like moving to a new house where you're told you can never clean or move the furniture without moving to a new house. It leaves the unfair impression that this is a "toy" db. "What other basic features is it missing if it doesn't have this?" Please don't think of it as trivial; first impression are very important.
- urthor 4y ago
  There's like 5 different features wants I could add like this. A columnar database engine comes to mind (slightly more work involved) and poaching a few Sqllite QOL features. Ultimately Postgres is a community driven product, and people want to work on what they want the work on. I don't get angry that some kind soul hasn't volunteered their Saturday for free for me.
  - pbz 4y ago
    Oh, absolutely; I'm just trying to raise the awareness/importance of this missing feature. Most devs that I know IRL that looked at PG had a very similar reaction to mine.
    - deleted 4y ago
      [deleted]
    - mixmastamyk 4y ago
      I'm receptive to the feature. But wouldn't it require locking the full table as it works? Seems like it would hurt availability of the biggest databases that would benefit most. Perhaps this is why it is thought somewhat impractical and therefore low-priority.
      - pbz 4y ago
        Depends on how the PG team decides to implement this feature. If they're OK with a virtual order (for "UI" purposes) while keeping the true order hidden then this would not require table locking (or it would be a very quick lock). If the order reflects the real / physical positioning then yes, it would probably require locking for a full table rebuild.
daigoba66 4y ago
Coming from an MS SQL Server background, this behavior surprised me. While SQL Server generally does treat null values as not equal in query predicates, for the purpose of unique constraints/indexes it’ll treat them as the same value. I guess this does go against the spec? But it makes sense as a human when looking at a tuple and deciding if it is “unique” or not. On that note, now I wonder what SQL Server does with nulls in a DISTINCT or GROUP BY. I suspect that it’ll treat all nulls as the same.
- hans_castorp 4y ago
  > Coming from an MS SQL Server background, this behavior surprised me. Oracle behaves the same as Postgres with regards to NULL values in unique indexes. > I guess this does go against the spec? The first comment (by the blog author) https://news.ycombinator.com/item?id=32054151 https://news.ycombinator.com/item?id=32054151 explained this: > The background here is that the SQL standard was ambiguous about which of the two ways an implementation should behave. So in the upcoming SQL:202x, this was addressed by making the behavior implementation-defined and adding this NULLS [NOT] DISTINCT option to pick the other behavior.
- hotsauceror 4y ago
  An interesting question. They may be treated differently depending on whether the nullable field is one of the grouping fields, or is one of the selected aggregate fields. https://blog.sqlauthority.com/2015/02/13/sql-server-warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation/?amp https://blog.sqlauthority.com/2015/02/13/sql-server-warning-...
hagope 4y ago
This is cool, but wouldn't creating a constraint using a nullable column be considered a poor design decision? In which scenarios would this be a good idea?
- cptn_badass 4y ago
  When an entry can belong to 0 or 1 related object only. Not that I'd put a constraint in such scenario, but I imagine a User can optionally have a Subscription, so subscription_id is either nil or present, and said subscription cannot be associated to any other User.
  - hagope 4y ago
    in that scenario why not use foreign key? any advantage to using constraint?
    - Pxtl 4y ago
      You do both. The foreign key only gaurantees that the other entity exists. The unique constraint ensures that only one pair of entities has this relationship, preventing a one-to-many binding. The distinctness of NULL allows you to have multiple entities with the same NULL value without violating the above UNIQUE constraint. The "NULL is empty" vs "NULL is unknown" is a series of trade-offs of labor-saving. Imho, the wrong trade-offs were made, but once the choice is made it makes sense to continue and be consistent with it. I'd rather be consistently wrong than inconsistently right.
      - hagope 4y ago
        If the entity doesn't exist, wouldn't it violate the FK and therefore no need for the nullable constraint?
zeroimpl 4y ago
I think this is a great change. I wish NULLS NOT DISTINCT was the default already - if I wanted the constraint to ignore null values I’d have used a partial index to only index non-null values. Doing the opposite is much more difficult.
- masklinn 4y ago
  It will never be the default.
irrational 4y ago
This is amazing. We moved from Oracle to Postgres after being on Oracle for 15+ years. That was a huge move that took 2 years. One of the biggest hurdles was rewriting thousands of sql queries. One of the biggest things we had to change was accounting for how oracle handled nulls versus how Postgres handled nulls. However, we missed this nuance about how Postgres treated nulls and unique. It wasn’t until about 2 years into using Postgres, and running into so many issues, that we finally stumbled upon this nuance in the documentation. Crap. That required making a bunch of more changes to account for this. At this point I’m firmly convinced that null handling is the hardest part of database work ;-)
layer8 4y ago
> Because NULL values are of unknown equality to one another, they do not violate UNIQUE constraints. Well, arguably you don’t know whether they violate uniqueness, so inserting more than one shouldn’t have been allowed.
jeff-davis 4y ago
In any thread involving SQL NULL, I see a lot of not-quite-right explanations of what SQL NULL is, conceptually. I challenge anyone who feels like they understand NULL conceptually to explain the following query: -- find orders with a total less than $10000 select order_id, sum(price) from orders o left join order_lineitems l using (order_id) group by order_id having sum(price) < 10000; This query is actually incorrect. Orders with no line items at all are clearly less than $10000, but they will be excluded because: first, the left outer join produces a NULL for the price; second, the group aggregation with SUM over that NULL will result in NULL; and third, the HAVING clause treats that NULL as false-like and excludes the order from the result. Of course, we can explain procedurally what's happening here, and each individual step makes some sense. But the end result has no conceptual integrity. Extra challenge: explain why using COUNT instead of SUM in the query does correctly return orders with fewer than 4 items: -- find orders with fewer than 4 line items select order_id, count(price) from orders o left join order_lineitems l using (order_id) group by order_id having count(price) < 4; PS: thank you to the author for a developer-friendly feature that adds flexibility here!
- masklinn 4y ago
  I’m not sure what explanation you want, NULL is in essence “not a value”, which works more or less the same way “not a number” does. So yes if you perform operations between a NULL and a value you get a NULL, therefore when you sum or compare NULLs with or to other things you get NULL, which is then treated as false in a boolean context. If the price of one item is UNKNOWN (which is what NULL represents in SQL) then it stands to reason that the sum is unknown, and it is unknown whether the sum is or is not smaller than 10000. > But the end result has no conceptual integrity. > Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out? > Extra challenge: explain why using COUNT instead of SUM in the query does correctly return orders with fewer than 4 items: You don’t need to determine the values of the sequence to see that there are 4 of them, therefore count doesn’t care that some of the items are null.
kindkid 4y ago
Cool. I remember having to jump through hoops in order to get the behavior I wanted in Oracle many years ago: https://stackoverflow.com/questions/675398 https://stackoverflow.com/questions/675398

428 points by Twisell 4y ago

> This fits with my mental model of how I think unique constraints should work with null values. Agree, this should be the default for unique indexes. Why would you ever want multiple NULLs in an unique index?

mhaberl 4y ago
> Why would you ever want multiple NULLs in an unique index? You would maybe want some value to be unique if it exists.
daveoc64 4y ago
Some kind of secondary identifier, but only for records where it is applicable (e.g. loyalty card number).
phoe-krk 4y ago
The rows (1, NULL), (1, NULL), and (1, NULL) - each NULL signifies an absence of a value - these NULLs may actually be later filled into (1, 1), (1, 2), and (1, 3). That's why may not know if any two NULL values are equal to one another. In some cases the above is actually important, whereas in others you may want to treat NULL as a "value" instead.
larsnystrom 4y ago
Multiple NULLs in a unique index are useful when an entity has 0 or 1 of a simple scalar value. For example, you might have a column with an external ID, such as a facebook login id in your users table. Not all users have a facebook id, but those who do should have a unique value in that column.
- radiospiel 4y ago
  You might want to look at a unique partial index: "CREATE UNIQUE INDEX foo ON users(facebook_id) WHERE facebook_id IS NOT NULL". I am not sure if this really is a great modelling. If a user happens to have two logins in your table (maybe they signed up with two different emails), and then connects one of his accounts with his FB identity, what does it mean that the other entry cannot be connected to the FB identity?
masklinn 4y ago
Because it’s very common for attributes to be optional but unique if specified e.g. your fleet management might have a 0-1:1 relationship between employees and cars, no car can be associated with 2 employees, an employee can’t have two cars, but any number of employee can be car-less, or car employee-less (lending pool, or cars which have not been attributed yet). This also fits perfectly well with the normal interpretation / treatment of sql null as “not a value”.
- faho 4y ago
  But an employee can't be car-less twice! That's what this change prohibits.
  - masklinn 4y ago
    > But an employee can't be car-less twice! What. > That's what this change prohibits. This change prohibits nothing, it allows specifying something (which was a bit complicated to enforce before).
    - faho 4y ago
      >This change prohibits nothing, it allows specifying something (which was a bit complicated to enforce before). It allows specifying a constraint - that you can't have two rows with the same values even if one of the values is a NULL. That's prohibiting duplicate NULLs. The change allows you to prohibit duplicate NULLs. Say you have a table (EmployeeName, CarID). You could do a UNIQUE constraint on those two attributes, but that would still allow: EmployeeName | CarID Jeff | 2 Kim | NULL Kim | NULL Here, "Kim" is car-less (NULL in the CarID field) twice, which makes no sense. Hence the new constraint.
      - masklinn 4y ago
        > It allows specifying a constraint - that you can't have two rows with the same values even if one of the values is a NULL. That's prohibiting duplicate NULLs. The change allows you to prohibit duplicate NULLs. Sure. > Here, "Kim" is car-less (NULL in the CarID field) twice, which makes no sense. The relation makes no sense to start with, it didn’t need duplicate nulls for that.
seanhunter 4y ago
One way nullable columns are often used is like an option type. It's easy to see that you might want rows to be unique if they have Some(value) but have multiple rows where the column is None
- almog 4y ago
  I agree that this is often the use case but depending on how the query (as well as how sparse the nulls are, the total data size and partitioning scheme I guess) it's also possible to achieve this through a join-table without introducing NULL values.
  - gsk22 4y ago
    Unless you outer join, in which case NULL values pop up out of thin air...
    - almog 4y ago
      Good point. I did try to say that whether that's a viable technique would depend on your query (but omitted some verb that should have made it better resemble a sentence in English :)). But yes, even if no nulls are present in any table, outer join could (or maybe even should) introduce them.
goto11 4y ago
Lets say you have a Person table with with a SSN column. The column has a unique constraint because no two people have the same SSN. But the column in nullable, since not everyone has a SSN. It would be weird to expect that only one person does not have SSN.
jhugo 4y ago
I can't think of a situation where I'd want "only a single NULL"; that seems much weirder. Another comment described a tree data structure where it could make sense, but I can't think of others. Nullable unique indexes usually describe some kind of optional identifier; if it's present, you want it to be unique, but it's perfectly fine for more than one row to omit it.
- orthoxerox 4y ago
  You might want an index like this on some aggregate table. Like, shoe_sales_total, which is grouped by the id of the shop assistant that helped you with the selection. But customers that have marched straight to the till and asked for shoe polish result in records with a NULL for agent_id. With a regular unique constraint (month, agent_id), your aggregate table can end up with two records for (May-21, NULL). Of course, the workaround for such cases is simple: have a sentinel value for unassisted sales and make the column not nullable.
  - jhugo 4y ago
    Such "tables" would more typically be constructed as views (materialised if performance requires it) over underlying data. In this case the underlying data would have a row for each sale, with a normal nullable column for agent_id. The query that constructs the view would use GROUP BY, and since NULLs group together in GROUP BY it would work as you expect (grouping "no agent" sales together into a single record in the view). The database system thus handles ensuring data consistency, merely by using its features as they are designed to be used.
knorker 4y ago
E.g. if you have a "tax ID" column, and two people in your database don't have tax IDs because they're not <your country> tax payers. Or "payment vendor processing ID". You may have multiple entries not yet sent to the vendor, so they're NULL. This shows up literally all the time in SQL schemas.

rwmj 4y ago

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.

nicoburns 4y ago
Except that this change means that the behaviour of nulls is not weird anymore!
- masklinn 4y ago
  Null is still null, sql null is always “weird” wherever it exists.
  - gigatexal 4y ago
    Why is null weird? Null means unknown. It's perfectly valid.
    - masklinn 4y ago
      “Valid” and “weird” are orthogonal concepts. 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.
      - nicoburns 4y ago
        > 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.

ahmed_ds 4y ago

When something that fundamental changes what is the cascading effect? Do other functions refer UNIQUE/NULL or do they have their own versions of UNIQUE/NULL like code?

masklinn 4y ago
There is no fundamental change, this is an opt-in on a per-index basis. It does not change null’s identity, it only allows indexes to treat nulls as identical.

hrdwdmrbl 4y ago

Cool but this was trivially solved previously by excluding NULL from the index using a partial index. I'm more interested in problems that have no great solution, like taking the first element of a set of partitioned data. Right now you have to partition & rank, and then select the rank 1 elements in a subsequent query.

jhugo 4y ago
Maybe it's what you meant be "subsequent query", but you don't actually need to do it in a separate query; you can do the partition & rank in a CTE and select from the resulting table, all in a single query. It's quite tidy actually.
masklinn 4y ago
> Cool but this was trivially solved previously by excluding NULL from the index using a partial index. That seems like the opposite solution, treating NULLs as different in database systems which treat them as identical by default (which I think only includes MSSQL). In postgres, and most DBMS, using a partial index excluding NULL allows any number of rows to have a NULL value… which is the same behaviour as if you’re not excluding NULLs. The new opt-in behaviour is treating all nulls as the same value, and thus allowing a UNIQUE index to constraint them. This required two different partial indexes, or for constrained data subset in hacks like COALESCE-ing nulls to a sentinel.
_flux 4y ago
I don't think you understood what this does (or then I misunderstood this) but if you do that, then how are you going to get an index violation from having two NULL values in the index? Because that is what this allows: normally indexing NULL values permitted multiple NULL entries, but with this NULL is comparable to other NULLs, therefore you can have only one NULL value in the index. Previously I had worked this around with functional index on e.g. COALESCE(value_with_null, -1), but not always you have a good sentinel value easily available. A more complicated index (..CASE WHEN..) would solve that, but it's nice to have this directly expressible, seems efficient as well.
silon42 4y ago

petereisentraut 4y ago

I am the author of this feature. The background here is that the SQL standard was ambiguous about which of the two ways an implementation should behave. So in the upcoming SQL:202x, this was addressed by making the behavior implementation-defined and adding this NULLS [NOT] DISTINCT option to pick the other behavior. Personally, I think that the existing PostgreSQL behavior (NULLS DISTINCT) is the "right" one, and the other option was mainly intended for compatibility with other SQL implementations. But I'm glad that people are also finding other uses for it.

zigzag312 4y ago
> Personally, I think that the existing PostgreSQL behavior (NULLS DISTINCT) is the "right" one, and the other option was mainly intended for compatibility with other SQL implementations. Care to explain why you think NULLS DISTINCT is the "right" default behavior? What problems does it solve to warrant additional complexity by default?
- knorker 4y ago
  Not parent commenter, but to me it seems "obviously correct". As explained in the article NULL means "unknown". Let's say I have two people, and a "tax identifier" column. Let's say two people can't have the same tax id. Obviously "don't have one" or "unknown" fit very well in NULL, and two people can both be missing a tax id, even if those who have it need it to be unique.
  - chris_wot 4y ago
    Except that's the ambiguity of NULL in the standard. It doesn't just mean "unknown". It can also mean "no value". And you can clearly compare two elements with no value. Which is why nulls are so incredibly confusing.
    - knorker 4y ago
      Yeah that's true. All the database can say is "I don't have a value here". Not "there exists no value for this column". But this doesn't mean that two NULLs are the same. Two people with NULL tax IDs are not the same (one turns out to be unknown but existing, the other is a toddler without a tax ID). Ten people with NULL address don't live in the same house. Half declined to give address, the other half are homeless. Either way we can't send mail to them. If you use the database to have a UNIQUE constraint of "only one purchase per household" then it makes no sense to allow NULL addresses, but only allow the first NULL address purchaser to buy the thing. Or "sorry, in this hotel we only allow one guest at a time without a car, and someone else already doesn't have a car". Does that guest without a car actually have a car? I don't think that's something that the database can solve. Should databases have two separate values for unknown or no value? That sounds like a world of hurt, with two types of null.

ryanbrunner 4y ago

This is great! I have multiple places in our code where I've had to define multiple unique indexes (with conditionals) to get around this. It's definitely confusing behaviour the first time you encounter it. Honestly I think SQL already breaks it's own standard of "NULL = unknown". One example: If you do a LEFT OUTER JOIN and the joined data is missing, you get NULL in the resultset. But in that case, it's not "unknown" whether there are records that can be joined to, the answer is that there are definitively no records there. NULL is univerally considered by most programming languages to equal "not present" vs "unknown", and in many cases by SQL in practice. The ship has probably sailed on actually changing the default behaviour, but optional flags like these are a good step.

knorker 4y ago
NULL means "I don't have this data". It doesn't guarantee that the data doesn't exist anywhere. It's semantically ambiguous, yes. But it's not ambiguous that the database doesn't have the data. It's very clearly NULL. If "middle name" is NULL, then the database itself can't tell you if a person doesn't have a middle name, or if it's merely not yet been told the middle name. You could differentiate the two by saying empty string for "has no middle name"… except on Oracle VARCHAR2, where NULL is equal to empty string.
- ryanbrunner 4y ago
  That's fair - as I said in another comment, it breaks down with keys. I can't think of a great way to model "this row definitively does not have whatever is referenced in another table". Using anything other than NULL for that means you can't use proper foreign keys.
- Pxtl 4y ago
  > You could differentiate the two by saying empty string for "has no middle name"… except on Oracle VARCHAR2, where NULL is equal to empty string. This is what kills me. This seems like an obvious case where multiple flavors of NULL would make perfect sense. For example Ticket.CancellationDate isn't "unknown", it's this ticket is not cancelled. But SQL standard doesn't have a way to express "empty value" as distinct from "unknown value", so we're stuck working around the strange implications of using this square peg to fill a round hole. Oracle's bizarre "empty VARCHAR2s are NULL" is strange but is consistent with every other datatype that offers no way to say "empty" but "NULL". 0 is not the same as NULL int and is not the same as empty int. Mindate is not the same as NULL date and is not the same as empty date.

tzahifadida 4y ago

wow thanks!

pbz 4y ago

Since we have some PG devs here: Can we please have a way to reorder columns? Coming from MySQL, this is one of the first missing features one hits. It's like moving to a new house where you're told you can never clean or move the furniture without moving to a new house. It leaves the unfair impression that this is a "toy" db. "What other basic features is it missing if it doesn't have this?" Please don't think of it as trivial; first impression are very important.

urthor 4y ago
There's like 5 different features wants I could add like this. A columnar database engine comes to mind (slightly more work involved) and poaching a few Sqllite QOL features. Ultimately Postgres is a community driven product, and people want to work on what they want the work on. I don't get angry that some kind soul hasn't volunteered their Saturday for free for me.
- pbz 4y ago
  Oh, absolutely; I'm just trying to raise the awareness/importance of this missing feature. Most devs that I know IRL that looked at PG had a very similar reaction to mine.
  - deleted 4y ago
    [deleted]
  - mixmastamyk 4y ago
    I'm receptive to the feature. But wouldn't it require locking the full table as it works? Seems like it would hurt availability of the biggest databases that would benefit most. Perhaps this is why it is thought somewhat impractical and therefore low-priority.
    - pbz 4y ago
      Depends on how the PG team decides to implement this feature. If they're OK with a virtual order (for "UI" purposes) while keeping the true order hidden then this would not require table locking (or it would be a very quick lock). If the order reflects the real / physical positioning then yes, it would probably require locking for a full table rebuild.

daigoba66 4y ago

Coming from an MS SQL Server background, this behavior surprised me. While SQL Server generally does treat null values as not equal in query predicates, for the purpose of unique constraints/indexes it’ll treat them as the same value. I guess this does go against the spec? But it makes sense as a human when looking at a tuple and deciding if it is “unique” or not. On that note, now I wonder what SQL Server does with nulls in a DISTINCT or GROUP BY. I suspect that it’ll treat all nulls as the same.

hans_castorp 4y ago
> Coming from an MS SQL Server background, this behavior surprised me. Oracle behaves the same as Postgres with regards to NULL values in unique indexes. > I guess this does go against the spec? The first comment (by the blog author) https://news.ycombinator.com/item?id=32054151 https://news.ycombinator.com/item?id=32054151 explained this: > The background here is that the SQL standard was ambiguous about which of the two ways an implementation should behave. So in the upcoming SQL:202x, this was addressed by making the behavior implementation-defined and adding this NULLS [NOT] DISTINCT option to pick the other behavior.
hotsauceror 4y ago
An interesting question. They may be treated differently depending on whether the nullable field is one of the grouping fields, or is one of the selected aggregate fields. https://blog.sqlauthority.com/2015/02/13/sql-server-warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation/?amp https://blog.sqlauthority.com/2015/02/13/sql-server-warning-...

hagope 4y ago

This is cool, but wouldn't creating a constraint using a nullable column be considered a poor design decision? In which scenarios would this be a good idea?

cptn_badass 4y ago
When an entry can belong to 0 or 1 related object only. Not that I'd put a constraint in such scenario, but I imagine a User can optionally have a Subscription, so subscription_id is either nil or present, and said subscription cannot be associated to any other User.
- hagope 4y ago
  in that scenario why not use foreign key? any advantage to using constraint?
  - Pxtl 4y ago
    You do both. The foreign key only gaurantees that the other entity exists. The unique constraint ensures that only one pair of entities has this relationship, preventing a one-to-many binding. The distinctness of NULL allows you to have multiple entities with the same NULL value without violating the above UNIQUE constraint. The "NULL is empty" vs "NULL is unknown" is a series of trade-offs of labor-saving. Imho, the wrong trade-offs were made, but once the choice is made it makes sense to continue and be consistent with it. I'd rather be consistently wrong than inconsistently right.
    - hagope 4y ago
      If the entity doesn't exist, wouldn't it violate the FK and therefore no need for the nullable constraint?

zeroimpl 4y ago

I think this is a great change. I wish NULLS NOT DISTINCT was the default already - if I wanted the constraint to ignore null values I’d have used a partial index to only index non-null values. Doing the opposite is much more difficult.

masklinn 4y ago
It will never be the default.

irrational 4y ago

This is amazing. We moved from Oracle to Postgres after being on Oracle for 15+ years. That was a huge move that took 2 years. One of the biggest hurdles was rewriting thousands of sql queries. One of the biggest things we had to change was accounting for how oracle handled nulls versus how Postgres handled nulls. However, we missed this nuance about how Postgres treated nulls and unique. It wasn’t until about 2 years into using Postgres, and running into so many issues, that we finally stumbled upon this nuance in the documentation. Crap. That required making a bunch of more changes to account for this. At this point I’m firmly convinced that null handling is the hardest part of database work ;-)

layer8 4y ago

> Because NULL values are of unknown equality to one another, they do not violate UNIQUE constraints. Well, arguably you don’t know whether they violate uniqueness, so inserting more than one shouldn’t have been allowed.

jeff-davis 4y ago

In any thread involving SQL NULL, I see a lot of not-quite-right explanations of what SQL NULL is, conceptually. I challenge anyone who feels like they understand NULL conceptually to explain the following query: -- find orders with a total less than $10000 select order_id, sum(price) from orders o left join order_lineitems l using (order_id) group by order_id having sum(price) < 10000; This query is actually incorrect. Orders with no line items at all are clearly less than $10000, but they will be excluded because: first, the left outer join produces a NULL for the price; second, the group aggregation with SUM over that NULL will result in NULL; and third, the HAVING clause treats that NULL as false-like and excludes the order from the result. Of course, we can explain procedurally what's happening here, and each individual step makes some sense. But the end result has no conceptual integrity. Extra challenge: explain why using COUNT instead of SUM in the query does correctly return orders with fewer than 4 items: -- find orders with fewer than 4 line items select order_id, count(price) from orders o left join order_lineitems l using (order_id) group by order_id having count(price) < 4; PS: thank you to the author for a developer-friendly feature that adds flexibility here!

masklinn 4y ago
I’m not sure what explanation you want, NULL is in essence “not a value”, which works more or less the same way “not a number” does. So yes if you perform operations between a NULL and a value you get a NULL, therefore when you sum or compare NULLs with or to other things you get NULL, which is then treated as false in a boolean context. If the price of one item is UNKNOWN (which is what NULL represents in SQL) then it stands to reason that the sum is unknown, and it is unknown whether the sum is or is not smaller than 10000. > But the end result has no conceptual integrity. > Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out? > Extra challenge: explain why using COUNT instead of SUM in the query does correctly return orders with fewer than 4 items: You don’t need to determine the values of the sequence to see that there are 4 of them, therefore count doesn’t care that some of the items are null.

kindkid 4y ago

Cool. I remember having to jump through hoops in order to get the behavior I wanted in Oracle many years ago: https://stackoverflow.com/questions/675398 https://stackoverflow.com/questions/675398