PostgreSQL `EXCLUDE` constraint not preventing duplicate records as expected
I keep running into I'm reviewing some code and I'm having trouble with an `EXCLUDE` constraint in PostgreSQL that's supposed to prevent overlapping date ranges in my table, but it seems to allow duplicates... I'm using PostgreSQL version 14.1, and I've defined my table like this: ```sql CREATE TABLE bookings ( id SERIAL PRIMARY KEY, room_id INT NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, EXCLUDE USING GIST (room_id WITH =) WITH (&&) ); ``` The `EXCLUDE` constraint is intended to prevent overlapping bookings for the same room. However, when I attempt to insert the following records: ```sql INSERT INTO bookings (room_id, start_date, end_date) VALUES (1, '2023-10-01', '2023-10-05'); INSERT INTO bookings (room_id, start_date, end_date) VALUES (1, '2023-10-04', '2023-10-06'); ``` I expected the second insert to unexpected result, but it actually succeeds and both records are added to the table. I've verified that I'm using the GIST index properly and confirmed that the data types for `start_date` and `end_date` are correct. I've also tried running `VACUUM` and `ANALYZE` on the table, but that didn't help either. Is there something I'm missing or a specific configuration I need to check? How can I effectively enforce this constraint? This issue appeared after updating to Sql 3.11. Any feedback is welcome! For reference, this is a production mobile app. Has anyone else encountered this?