Saturday, March 19, 2016

Gist and Gin support for enums

Recently I submitted a patch (too late for release 9.6, unfortunately) to add support for enum data types to the btree_gist and btree_gin additional modules. This came out of a desire to be able to use an enum field in an exclusion constraint. That really only requires GiST support, but since I was adding enum support to btree_gist it seemed a good idea to add it to btree_gin as well, so that, for example, enum fields can be used in multi-column GIN indexes.

Here's an exclusion constraint example using enums, adapted from the Postgres docs.

andrew=# create extension btree_gist;
CREATE EXTENSION
andrew=# \dx
                           List of installed extensions
    Name    | Version |   Schema   |                  Description                  
------------+---------+------------+-----------------------------------------------
 btree_gist | 1.2     | public     | support for indexing common datatypes in GiST
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

andrew=# create type animal as enum ('zebra','lion');
CREATE TYPE
andrew=# create table zoo (cage int, exhibit animal, exclude using gist(cage with =, exhibit with <>));
CREATE TABLE
andrew=# INSERT INTO zoo VALUES(123, 'zebra');
INSERT 0 1
andrew=# INSERT INTO zoo VALUES(123, 'zebra');
INSERT 0 1
andrew=# INSERT INTO zoo VALUES(123, 'lion');
ERROR:  conflicting key value violates exclusion constraint "zoo_cage_exhibit_excl"
DETAIL:  Key (cage, exhibit)=(123, lion) conflicts with existing key (cage, exhibit)=(123, zebra).
andrew=# 

5 comments:

  1. Do you mean "too late for 9.5"? Otherwise I fail to see why this couldn't make 9.6?

    ReplyDelete
  2. We are several weeks past the date for accepting new features for 9.6.

    ReplyDelete
  3. Extremely happy to see this in. Fingers crossed that we get both this and UUID support in 9.7 (if UUID support isn't in 9.6 that is, last I saw it was stalled). It's been a pain having to work around the limitation for exclusion constraints in my databases.

    Thanks for the work on this!

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete