Hackers,
I am trying to do something like this:
CREATE TYPE source AS ENUM(
'fred', 'wilma', 'barney', 'betty'
);
CREATE EXTENSION btree_gist;
CREATE TABLE things (
source source NOT NULL,
within tstzrange NOT NULL,
EXCLUDE USING gist (source WITH =, within WITH &&)
);
Alas, enums are not supported by btree_gist:
try.sql:13: ERROR: data type source has no default operator class for
access method "gist"
HINT: You must specify an operator class for the index or define a default
operator class for the data type.
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?
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.
I guess I can create my own IMMUTABLE function over the ENUM:
CREATE FUNCTION source_to_text(
source
) RETURNS TEXT LANGUAGE sql STRICT IMMUTABLE AS $$
SELECT $1::text;
$$;
So this works:
EXCLUDE USING gist (source_to_text(source) WITH =, within WITH &&)
So I guess that’s good enough for now. But should :: really be a syntax error
in index expressions?
Thanks,
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
