CAST Within EXCLUSION constraint

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

Responses

Browse pgsql-hackers by date

  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])