Postgres generated column is not immutable

Question

I am adding some columns to a table, and want a generated column that combines them together that I'll use for a unique index. When I try to add the column, I get the error ERROR: generation expression is not immutable.

I followed the solution from this question, and am specifically using CASE and || for string concatenation, which are supposed to be immutable.

ALTER TABLE tag
  ADD COLUMN prefix VARCHAR(4) NOT NULL,
  ADD COLUMN middle BIGINT NOT NULL,
  ADD COLUMN postfix VARCHAR(4), -- nullable
  -- VARCHAR size is 4 prefix + 19 middle + 4 postfix + 2 delimiter
  ADD COLUMN tag_id VARCHAR(29) NOT NULL GENERATED ALWAYS AS
    (CASE WHEN postfix IS NULL THEN prefix || '-' || middle
          ELSE prefix || '-' || middle || '-' || postfix
          END
    ) STORED;
CREATE UNIQUE INDEX unq_tag_tag_id ON tag(tag_id);

---

In the postgres mailing list, one of the contributors clarifies that:

integer-to-text coercion, [...] isn't necessarily immutable

However, he doesn't share an integer-to-text function that is immutable. Does anyone know if one exists?

Answer 1

Test in 14.1 shows that the cause is the implicite conversion of the bigint column to text in the concatenation (even without a case)

An explicite cast to text produces no error - middle::text

ALTER TABLE tag
  ADD COLUMN prefix VARCHAR(4) NOT NULL,
  ADD COLUMN middle BIGINT NOT NULL,
  ADD COLUMN postfix VARCHAR(4), -- nullable
  -- VARCHAR size is 4 prefix + 19 middle + 4 postfix + 2 delimiter
  ADD COLUMN tag_id VARCHAR(29) NOT NULL GENERATED ALWAYS AS
    (CASE WHEN postfix IS NULL THEN prefix || '-' || middle::text
          ELSE prefix || '-' || middle::text || '-' || postfix
          END
    ) STORED;