Re: Generated column and string concatenation issue

Manuel Rigger <rigger.manuel@gmail.com> writes:
> Consider the following statement:

> CREATE TABLE t0(c0 TEXT GENERATED ALWAYS AS (('abc' || 1)) STORED); --
> unexpected: generation expression is not immutable

No, that's not a bug.  What you've got is "text || integer", which
requires an integer-to-text coercion, which isn't necessarily
immutable.  (Well, actually, integer-to-text is immutable.  But
the particular operator you're getting here is textanycat which
accepts anything on the RHS, so it has to be marked stable which
is our worst-case assumption for the stability of I/O conversions.
As an example, timestamp-to-text's results vary with the DateStyle
GUC so that one definitely isn't immutable.)

You could imagine different factorizations of this functionality
that might allow the specific RHS type to be taken into account,
but the actual details of how to make that happen aren't very
clear, and changing it might have other downsides.

Anyway the short answer is that you should have done

CREATE TABLE t0(c0 TEXT GENERATED ALWAYS AS (('abc' || '1')) STORED);

which would resolve as "text || text" which is immutable.
The explicit cast that you showed also dodges the problem
by not relying on textanycat.

            regards, tom lane