Postgres Date Overlapping Constraint

Postgres date overlapping constraint

Ok i ended up doing this :

CREATE TABLE test (
    from_ts TIMESTAMPTZ,
    to_ts TIMESTAMPTZ,
    account_id INTEGER DEFAULT 1,
    product_id INTEGER DEFAULT 1,
    CHECK ( from_ts < to_ts ),
    CONSTRAINT overlapping_times EXCLUDE USING GIST (
        account_id WITH =,
        product_id WITH =,
        period(from_ts, CASE WHEN to_ts IS NULL THEN 'infinity' ELSE to_ts END) WITH &&
    )
);

Works perfectly with infinity, transaction proof.

I just had to install temporal extension which is going to be native in postgres 9.2 and btree_gist available as an extension in 9.1 CREATE EXTENSION btree_gist;

nb : if you don't have null timestamp there is no need to use the temporal extension you could go with the box method as specified in my question.

SQL CHECK constraint to prevent date overlap

In PostgreSQL 8.4 this can only be solved with triggers. The trigger will have to check on insert/update that no conflicting rows exist. Because transaction serializability doesn't implement predicate locking you'll have to do the necessary locking by yourself. To do that SELECT FOR UPDATE the row in the machines table so that no other transaction could be concurrently inserting data that might conflict.

In PostgreSQL 9.0 there will be a better solution to this, called exclusion constraints (somewhat documented under CREATE TABLE). That will let you specify a constraint that date ranges must not overlap. Jeff Davis, the author of that feature has a two part write-up on this: part 1, part 2. Depesz also has some code examples describing the feature.

How to prevent overlapping of int ranges

If you want to prevent an overlapping range you will have to use a range type not an array.

I also assume that start and end should never overlap on the same day, so you need to include the date column in the exclusion constraint:

CREATE TABLE appointments 
(
    id SERIAL PRIMARY KEY,
    date TIMESTAMP NOT NULL,
    start_mn INT NOT NULL,
    end_mn INT NOT NULL,
    EXCLUDE using gist( int4range(start_mn, end_mn, '[]') WITH &&, "date" with =)
)

If start_mn and end_mn are supposed to be "time of the day", then those columns should be defined as time, not as integers.

Postgres: best way to avoid overlapping ranges with other conditions

Take a look at:

https://www.postgresql.org/docs/12/rangetypes.html#RANGETYPES-INDEXING

using btree_gist example.

CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation (
    room text,
    during tsrange,
    EXCLUDE USING GIST (room WITH =, during WITH &&)
);

where you would substitute your FK values, so:

EXCLUDE USING GIST (fkey1 WITH =, fkey2 WITH =, daterange(date_start, date_end, '[]'::text) WITH &&)

assuming separate dates and inclusive upper date.

Prevent date overlap postgresql

You can do this with an exclusion constraint, using the overlap operator (&&) for the daterange type:

CREATE TABLE workouts (
  week_start DATE,
  week_end DATE,
  EXCLUDE USING gist (daterange(week_start, week_end) WITH &&)
)

Exclusion constraint that allows overlapping at the boundaries

Use ranges that do not include one of the ends:

daterange(c_from, c_until, '[)')

Then they won't conflict, even if one interval ends at the same point where another begins.

Create table with date/time range constraint to prevent overlapping

The phrase you want to google for is "postgresql exclusion constraint".

There is an example doing exactly what you want here.

http://www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-CONSTRAINT

---

Related Topics