4

For various reasons all my tables have UUIDs as primary keys and I need to exclude duplicate rows where the combination of area_id, user_id, and finished_at <-> started_at window.

The only way I can see to do this involves casting UUIDs to text inside the overlapping_allocations constraint below. However this seems stupidly wasteful, am I missing a trick?

This is all running on Postgres 9.3.5 and there is freedom to enable other common extensions.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "btree_gist";

CREATE TABLE areas(
    id uuid PRIMARY KEY default uuid_generate_v1(),
    name text NOT NULL UNIQUE,
    -- blah blah, lots of other fields
)

CREATE TABLE allocations(
    id uuid PRIMARY KEY default uuid_generate_v1(),
    area_id uuid references areas,
    user_id text NOT NULL,
    started_at timestamp with time zone NOT NULL,
    finished_at timestamp with time zone DEFAULT NULL,
    created_at timestamp with time zone default NOW(),
    CONSTRAINT valid_times CHECK (started_at < finished_at),
    CONSTRAINT overlapping_allocations EXCLUDE USING GIST (
        -- These two casts are horrid but GIST doesn't understand uuids
        -- see here https://stackoverflow.com/questions/22720130/how-to-use-uuid-with-postgresql-gist-index-type
        cast("area_id" AS text) WITH =,
        user_id WITH =,
        tstzrange(started_at, COALESCE(finished_at, 'infinity'::timestamptz) ) WITH &&
    )
);

1 Answer 1

2

As of Postgres 10 the btree_gist extension (in contrib) supports UUIDs, so if you add that extension first, you'll be able to create indexes and exclusion constraints without casting to text. Pre-10, casting to text is the way to go.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.