conflicting key value violates exclusion constraint

This error occurs when you try to insert or update data that conflicts with an exclusion constraint, commonly used for overlapping ranges.

The violates exclusion constraint error means your data conflicts with an exclusion constraint.

Understanding the Error

ERROR: conflicting key value violates exclusion constraint "reservations_room_during_excl"
DETAIL: Key (room_id, during)=(1, ["2024-03-15 10:00:00","2024-03-15 12:00:00")) conflicts with existing key (room_id, during)=(1, ["2024-03-15 09:00:00","2024-03-15 11:00:00")).

Exclusion constraints prevent overlapping or conflicting data.

Understanding Exclusion Constraints

SQL

-- Prevent overlapping reservations for same room
CREATE TABLE reservations (
  id SERIAL PRIMARY KEY,
  room_id INTEGER,
  during TSRANGE,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

This constraint ensures no two reservations for the same room overlap in time.

Common Causes

1. Overlapping Time Ranges

SQL

-- First booking: 9am-11am
INSERT INTO reservations (room_id, during)
VALUES (1, '[2024-03-15 09:00, 2024-03-15 11:00)');

-- Second booking overlaps: 10am-12pm
INSERT INTO reservations (room_id, during)
VALUES (1, '[2024-03-15 10:00, 2024-03-15 12:00)');  -- Error!

2. Overlapping Date Ranges

SQL

-- Employee assignments that shouldn't overlap
CREATE TABLE assignments (
  employee_id INTEGER,
  project_id INTEGER,
  period DATERANGE,
  EXCLUDE USING gist (employee_id WITH =, period WITH &&)
);

How to Fix It

Solution 1: Check Before Insert

SQL

-- Check for conflicts first
SELECT EXISTS (
  SELECT 1 FROM reservations
  WHERE room_id = 1
  AND during && '[2024-03-15 10:00, 2024-03-15 12:00)'
);

-- Only insert if no conflict
INSERT INTO reservations (room_id, during)
SELECT 1, '[2024-03-15 10:00, 2024-03-15 12:00)'
WHERE NOT EXISTS (
  SELECT 1 FROM reservations
  WHERE room_id = 1
  AND during && '[2024-03-15 10:00, 2024-03-15 12:00)'
);

Solution 2: Find Available Time

SQL

-- Find gaps in reservations
SELECT
  upper(r1.during) AS gap_start,
  lower(r2.during) AS gap_end
FROM reservations r1
JOIN reservations r2
  ON r1.room_id = r2.room_id
  AND upper(r1.during) < lower(r2.during)
WHERE r1.room_id = 1
AND NOT EXISTS (
  SELECT 1 FROM reservations r3
  WHERE r3.room_id = 1
  AND r3.during && tsrange(upper(r1.during), lower(r2.during))
);

Solution 3: Handle Conflict in Application

JAVASCRIPT

async function createReservation(roomId, startTime, endTime) {
  const during = `[${startTime},${endTime})`;

  try {
    await db.query(
      'INSERT INTO reservations (room_id, during) VALUES ($1, $2::tsrange)',
      [roomId, during]
    );
    return { success: true };
  } catch (error) {
    if (error.code === '23P01') {
      return { success: false, error: 'Time slot is already booked' };
    }
    throw error;
  }
}

Solution 4: Split or Adjust Range

SQL

-- Delete conflicting and insert adjusted
BEGIN;
DELETE FROM reservations WHERE room_id = 1 AND during && $1;
INSERT INTO reservations (room_id, during) VALUES (1, $1);
COMMIT;

Solution 5: Use Adjacent Ranges

SQL

-- Allow adjacent but not overlapping
-- Use -|- (adjacent) instead of && (overlaps)
EXCLUDE USING gist (room_id WITH =, during WITH -|-)

Enabling Extensions

SQL

-- btree_gist extension needed for combining types
CREATE EXTENSION IF NOT EXISTS btree_gist;

Best Practices

Check availability before attempting insert
Use transactions for check-then-insert patterns
Handle conflicts gracefully in application
Index appropriately for range queries
Use proper range types (TSRANGE, DATERANGE, etc.)