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;

Answer 2

Marmite Bomber's answer shows the soluion; let me add an explanation.

There are two concatenation operators for text:

SELECT oid, oprname,
       oprleft::regtype,
       oprright::regtype,
       oprcode
FROM pg_operator
WHERE oprname = '||'
  AND oprleft = 'text'::regtype;

 oid  │ oprname │ oprleft │  oprright   │  oprcode   
══════╪═════════╪═════════╪═════════════╪════════════
  654 │ ||      │ text    │ text        │ textcat
 2779 │ ||      │ text    │ anynonarray │ textanycat
(2 rows)

The first operator concatenates text with text, the second concatenates text with anything else.

Let's examine the volatility of these two functions:

SELECT oid, proname, provolatile
FROM pg_proc
WHERE pronamespace = 'pg_catalog'::regnamespace
  AND proname IN ('textcat', 'textanycat');

 oid  │  proname   │ provolatile 
══════╪════════════╪═════════════
 1258 │ textcat    │ i
 2003 │ textanycat │ s
(2 rows)

So if you concatenate text and bigint, that operation is not IMMUTABLE, but casting the bigint to text first makes the operation IMMUTABLE.

It is not because of concatenating with integers that this operator is not immutable. But anynonarray could be any data type like timestamp with time zone, whose string representation depends on the current setting of timezone.