Re: Generated column and string concatenation issue

Thanks for the clarification! Do you think that's clear from the documentation?

Best,
Manuel

On Wed, Jul 10, 2019 at 4:47 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Manuel Rigger <rigger(dot)manuel(at)gmail(dot)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