I understand the edge case and solution, but I personally have never run into this. I suppose when I put a uniqueness constraint on a column, I always expect it to have a value (and thus I also make is not null). I have a hard time thinking of a real use case where you only want one null value in a column.
I’ve often relied on NULL not being unique in unique constraints on multiple columns, some of which optional. For example, if you’re using a kind of polymorphism, you might have a set of foreign key columns, which are supposed to be be unique combined with some other value. But yeah, just for a single column it’s a bit weird.
Perhaps if you have a table storing the results of something like
SELECT w, x, y, sum(z)
FROM foo
GROUP BY CUBE (w, x, y)
so you want to ensure that there is (e.g.) only one row with all NULLs for w, x, and y. You could do this with a materialized view, but unfortunately those don’t support partial updates.
It looks like this just creates appropriate triggers automatically, so this is something you could do manually right now. I believe triggers are generally not as efficient when doing bulk inserts since they operate on each row individually. So if you are updating a bunch of rows you may get a better plan by writing out the query manually.
so this is something you could do manually right now
Yes, but imagine how unwieldy and fragile it would get when there are multiple joins and aggregates involved.
since they operate on each row individually
Since Postgres 10(look for “AFTER trigger transition tables”), AFTER triggers can operate on all the changed rows in a statement in bulk. Here’s an example. I don’t know where I read it (Edit: here it is), but AFAIK, this feature was actually added in preparation for the upcoming incremental view maintenance feature.
I understand the edge case and solution, but I personally have never run into this. I suppose when I put a uniqueness constraint on a column, I always expect it to have a value (and thus I also make is
not null). I have a hard time thinking of a real use case where you only want one null value in a column.I’ve often relied on
NULLnot being unique in unique constraints on multiple columns, some of which optional. For example, if you’re using a kind of polymorphism, you might have a set of foreign key columns, which are supposed to be be unique combined with some other value. But yeah, just for a single column it’s a bit weird.Perhaps if you have a table storing the results of something like
so you want to ensure that there is (e.g.) only one row with all NULLs for w, x, and y. You could do this with a materialized view, but unfortunately those don’t support partial updates.
Here’s hoping incremental view management makes it to Postgres 15!
It looks like this just creates appropriate triggers automatically, so this is something you could do manually right now. I believe triggers are generally not as efficient when doing bulk inserts since they operate on each row individually. So if you are updating a bunch of rows you may get a better plan by writing out the query manually.
Yes, but imagine how unwieldy and fragile it would get when there are multiple joins and aggregates involved.
Since Postgres 10(look for “AFTER trigger transition tables”),
AFTERtriggers can operate on all the changed rows in a statement in bulk. Here’s an example. I don’t know where I read it (Edit: here it is), but AFAIK, this feature was actually added in preparation for the upcoming incremental view maintenance feature.Very neat. I had held off on using triggers because of this issue. I’ll definitely look into this.
Wow nice, I have run into this when I had a 3 way join table where some foreign keys could be null and I needed to treat the NULLS as the same.
The work around was to create a
UNIQUE INDEXcombined by coalescing the null values to-1or""e.g.
And then during
INSERTstatements, adding:But it is a gotcha as someone might forget so it feels a little fragile!
Yes, it’s fragile. After all,
-1is a valid id.