On Tue, Aug 20, 2013 at 8:53 PM, David E. Wheeler <[email protected]>wrote:
> On Aug 20, 2013, at 6:50 PM, Tom Lane <[email protected]> wrote: > > > You need more parentheses -- (source::text) would've worked. > > Alas, no, same problem as for CAST(): > > ERROR: functions in index expression must be marked IMMUTABLE > > >> 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. > > Ah, right. Maybe if there was a way to get at some immutable numeric value… > It seems reasonable to me to cast enum to oid. However, creating casts without function isn't allowed for enums. test=# create cast (source as oid) without function; ERROR: enum data types are not binary-compatible However, this restriction can be avoided either by writing dummy C-function or touching catalog directly: test=# insert into pg_cast values ((select oid from pg_type where typname = 'source'), (select oid from pg_type where typname = 'oid'), 0, 'e', 'b'); INSERT 341001 1 Then you can define desired restriction. CREATE TABLE things ( source source NOT NULL, within tstzrange NOT NULL, EXCLUDE USING gist ((source::oid) WITH =, within WITH &&) ); Probably, I'm missing something and casting enum to oid is somehow unsafe? ------ With best regards, Alexander Korotkov.
