1

currently to stop overlapping timestamps of specific event_type in following table:

    CREATE TABLE foo (
        id                    UUID PRIMARY KEY,
        event_type            TEXT,
        from                  timestamp WITH time zone NOT NULL,
        to                    timestamp WITH time zone NOT NULL,
        deleted               timestamp WITH time zone DEFAULT NULL
    );

i'm using:

    ALTER TABLE foo
        ADD CONSTRAINT no_overlapping_times_for_event_type EXCLUDE
        USING gist (event_type WITH =, tstzrange(from, to, '[)') WITH &&);

Above works fine.

Question:

How do i make it so that the constraint does not kick in if the foo had deleted value populated? so that i can create delete one and create a new one with same dates without the constraint stopping me. I only want it to stop user from creating overlapping timerange entries of same event_type only if they're not deleted.

0

You can add a WHERE clause to the constraint, making it a partial one. It will be checked only for row that have deleted IS NULL. Rows that have deleted populated will be completely ignored by the constraint checks:

ALTER TABLE foo
    ADD CONSTRAINT no_overlapping_times_for_event_type EXCLUDE
    USING gist (event_type WITH =, tstzrange("from", "to", '[)') WITH &&)
    WHERE (deleted IS NULL) ;
  • from and to are reserved keywords. I suggest you use some other names for these columns (eg. ts_from, ts_to) if you aren't already doing so. – ypercubeᵀᴹ May 16 '18 at 1:17
  • oh yes that works! what a boss. cheers mate – ke3pup May 16 '18 at 1:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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