substring(enumColumn::TEXT from 'pattern') not IMMUTABLE in PostgreSQL

Question

As I understand it, the substring function should be immutable. However, when I try to generate a column from the expression substring(enumColumn::TEXT from '-(.+)') I get a generation expression is not immutable error.

enumColumn is constrained to not be NULL, and it is not a generated column. Its type is an enum whose values are strings that all match the pattern '-(.+)'.

Is the issue related to the possibility that the pattern won't match and return NULL? Or is PostgreSQL complaining about something else?

UPDATE: this works fine when the column type from which the substring is computed is already TEXT. I think the issue is that enumType::TEXT is only STABLE, not IMMUTABLE?

Thanks @AdrianKlaver. As far as I can tell, the framework I'm using doesn't support user-defined functions. I guess that means I'm SOL? — Mackie Messer
– Mackie Messer, Commented Aug 28 at 21:57
Typecasting is potentially affected by some configuration variables, for example time_zone. So it's STABLE but not IMMUTABLE. — Bill Karwin
– Bill Karwin, Commented Aug 28 at 22:04
Maybe not, I got to thinking about this and I vaguely remember casting enums. Using the example from here Enum type I could do : SELECT name, substring(current_mood::TEXT from '-(.+)') FROM people; without issue. Where are you running this query? Also can add a simple test case to the question? Also what version of Postgres and where did you get it from? — Adrian Klaver
– Adrian Klaver, Commented Aug 28 at 22:14

Laurenz Albe · Accepted Answer · 2025-08-29 05:37:35Z

3

Yes, it is the type cast that is your problem. The only solution would be to define a function (or a type cast using a function) and explicitly declare the function to be IMMUTABLE. You can safely do that if you promise never to change an enum label with ALTER TYPE ... RENAME.

A different approach would be not to use a generated column, but to define a BEFORE INSERT OR UPDATE trigger that sets the column to the desired value before the row is inserted into the table.

answered Aug 29 at 5:37

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

substring(enumColumn::TEXT from 'pattern') not IMMUTABLE in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related