1. 20
  1. 3

    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.

    1. 1

      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.

      1. 1

        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.

        1. 1

          but unfortunately those don’t support partial updates

          Here’s hoping incremental view management makes it to Postgres 15!

          1. 1

            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.

            1. 2

              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.

              1. 1

                Very neat. I had held off on using triggers because of this issue. I’ll definitely look into this.

      2. 1

        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 INDEX combined by coalescing the null values to -1 or ""


        CREATE UNIQUE INDEX your_idx ON table USING btree (col1, COALESCE(col2, -1), COALESCE(col3, -1), col4);

        And then during INSERT statements, adding:

        ON CONFLICT (col1, COALESCE(col2, -1), COALESCE(col3, -1), col4) …

        But it is a gotcha as someone might forget so it feels a little fragile!

        1. 1

          Yes, it’s fragile. After all, -1 is a valid id.