Re: CAST Within EXCLUSION constraint - Mailing list pgsql-hackers

"David E. Wheeler" <david@justatheory.com> writes:
> Well, maybe I can cast it? But no, changing the EXCLUDE line to
>         EXCLUDE USING gist (source::text WITH =, within WITH &&)
> Yields a syntax error:
>     try.sql:13: ERROR:  syntax error at or near "::"
>     LINE 4:     EXCLUDE USING gist (source::text WITH =, within WITH &&)
> So that's out. Why shouldn't :: be allowed?

You need more parentheses -- (source::text) would've worked.

> No problem, I can use CAST(), right? So I try:
>         EXCLUDE USING gist (CAST(source AS text) WITH =, within WITH &&)
> Not so much:
>     try.sql:13: ERROR:  functions in index expression must be marked IMMUTABLE
> I guess it's because locale settings might change, and therefore change the text representation? Seems unlikely,
though.

Not locale, just renaming one of the values would be enough to break that.
Admittedly we don't provide an official way to do that ATM, but you can do
an UPDATE on pg_enum.
        regards, tom lane