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?

Good question. I never really got to understand why this example works for VARCHARs but not for INTs. — The Impaler, Apr 27 '23 at 17:25
Seems that it works with cast to `text`, i.e. `middle::text` — Marmite Bomber, Apr 27 '23 at 17:49

Laurenz Albe · Answer 1 · 2023-04-28T05:32:53.097

5

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.

edited Apr 28 '23 at 05:32

answered Apr 27 '23 at 18:13

Laurenz Albe

209,280
17
206
263

Thanks for the inner look at the engine. However, I still fail to conceptually understand why concatenating an INT could make it immutable. Can you show an example? Or... maybe there should be a third operator option for the (text. int) variation? – The Impaler Apr 27 '23 at 23:40
1

@TheImpaler I have added an explanation to the answer. – Laurenz Albe Apr 28 '23 at 05:33
Thank you for the note. IMHO, I think the second case should be separated in two different ones, since BIGINT behaves quite differently compared to TIMESTAMP WITH TIME ZONE. – The Impaler Apr 28 '23 at 13:30
@TheImpaler Are you suggesting that we have a concatenation function for each data type (or at least for each preferred data type)? You are welcome to submit a patch... – Laurenz Albe Apr 28 '23 at 14:58
You are right. Complaning is easy; taking action, on the other hand, requires much more effort. Cannot do it right now, but I would love to help with PostgreSQL in the future. Thanks for the *scolding* ;) – The Impaler Apr 28 '23 at 16:56

score 4 · Accepted Answer · answered Apr 27 '23 at 17:55

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;

Postgres generated column is not immutable

2 Answers2