From: | "David E(dot) Wheeler" <david(at)justatheory(dot)com> |
---|---|
To: | pgsql-hackers Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | CAST Within EXCLUSION constraint |
Date: | 2013-08-20 15:49:27 |
Message-ID: | 3C24671E-AA46-4B4D-965F-1F7F371F1F5E@justatheory.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tarvi Pillessaar | 2013-08-20 16:21:41 | Detail part for still waiting for lock log message |
Previous Message | Alvaro Herrera | 2013-08-20 15:47:50 | Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review]) |