Why can't postgres index a text casted enum

Question

I'm trying to add a default value to an enum column so a unique constraint will only allow one null like so:

CREATE UNIQUE INDEX "some_index" on some_table (coalesce(some_enum_column::text, 'some_default_value'));

Basically, I'm trying to replace Postgres-15's new NULLS NOT DISTINCT feature in Postgres-14.5

And I'm getting this error:

functions in index expression must be marked IMMUTABLE

From reading online and from this answer, I understand that this cast is problematic because the enum values can change, making this cast mutable.

But if this is the case, how does Postgres index an enum column normally? What is the difference between the enum and its string representation in this case?

Does it have some trigger to reindex on enum change?

I could use a partial index in this example but in reality, my index includes multiple columns, some nullable and some not.

I'm writing an extension to an ORM, so I need to be able to support an arbitrary amount of nullable columns.

Answer 1

If you want to emulate NULLS NOT DISTINCT behaviour in Postgres version 14 (or earlier), you can use a partial index with a constant value.

-- a normal UNIQUE index for the non-null values
create unique index t_x_nulls_uq 
    on some_table (some_enum_column) ;

-- and a partial UNIQUE index on NULL values, allowing only a single NULL
create unique index t_x_nulls_somecol_uq 
    on some_table ((true))
    where some_enum_column is null ;

Tested in dbfiddle.uk

---

If there are multiple columns, say 5 columns with 3 non-nullable and 2 nullable, you'd have to use more indexes. I.e.:

-- columns: a, b, c, d, e
-- not null columns: a, b, c
-- nullable columns: d, e

-- a normal UNIQUE index for the non-null values
create unique index t_abc_de_nulls_uq 
    on some_table (a, b, c, d, e)
    where d is not null
      and e is not null ;

-- a partial UNIQUE index on NULL values of column d,
-- allowing only a single NULL on d
create unique index t_abc_e_nulls_d_uq 
    on some_table (a, b, c, e)
    where d is null 
      and e is not null ;

-- a partial UNIQUE index on NULL values of column e,
-- allowing only a single NULL on e
create unique index t_abc_d_nulls_e_uq 
    on some_table (a, b, c, d)
    where d is not null
      and e is null ;

-- a partial UNIQUE index on NULL values of columns d and e,
-- allowing only a single row with both d and e NULL
create unique index t_abc_nulls_de_uq 
    on some_table (a, b, c)
    where d is null
      and e is null ;

If this is required on a combination of N nullable columns (the number of non-nullable columns is irrelevant) you'll need 2^N indexes.

It's also kind of obvious that it would be better (or at least normalized) to have these as separate tables. Using NULL as a special mark leads to many kinds of problems.