for timestamp range, enforce no_adjacent, no_overlap, enforce_bounds constraints.

tsrange constraint.sql

--using constraint make 2 timestamp range not overlap.
--using constraint make 2 timestamp range not adjacent.
--using constraint make rtimestamp range include range lower bound, exclude upper bound.
/*
https://dbfiddle.uk/KyOD2a58
Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
https://stackoverflow.com/questions/19504727/preventing-adjacent-overlapping-entries-with-exclude-in-postgresql/19505869#19505869
*/
DROP TABLE test_gist_lap;
BEGIN;
CREATE TABLE test_gist_lap (
    tbl_id bigserial PRIMARY KEY,
    tsr tsrange,
    CONSTRAINT tsr_no_overlap
    EXCLUDE USING gist (tsr WITH &&), CONSTRAINT tsr_no_adjacent
    EXCLUDE USING gist (tsr WITH -|-), CONSTRAINT tsr_enforce_bounds CHECK (lower_inc(tsr) AND NOT upper_inc(tsr))
);
INSERT INTO test_gist_lap (tsr)
    VALUES ('[2023-10-22 00:00, 2023-10-22 01:00)'),
    ('[2023-10-22 02:00, 2023-10-22 03:00)');
END;
-- Succeeds
INSERT INTO test_gist_lap (tsr)
    VALUES ('[2023-10-22 06:00, 2023-10-22 07:00)');
TABLE test_gist_lap;

-- Conflict with tsr_no_overlap
INSERT INTO test_gist_lap (tsr)
    VALUES ('[2023-10-22 00:00, 2023-10-22 04:00)');
/*
ERROR:  conflicting key value violates exclusion constraint "tsr_no_overlap"
DETAIL:  Key (tsr)=(["2023-10-22 00:00:00","2023-10-22 04:00:00")) conflicts with existing key (tsr)=(["2023-10-22 00:00:00","2023-10-22 01:00:00")).
 */

-- Conflict with tsr_no_adjacent
INSERT INTO test_gist_lap (tsr)
    VALUES ('[2023-10-22 03:00, 2023-10-22 04:00)');
/*
ERROR:  conflicting key value violates exclusion constraint "tsr_no_adjacent"
DETAIL:  Key (tsr)=(["2023-10-22 03:00:00","2023-10-22 04:00:00")) conflicts with existing key (tsr)=(["2023-10-22 02:00:00","2023-10-22 03:00:00")).
 */

-- avoid conflicts, nothing will happen. no rows will be inserted.
INSERT INTO test_gist_lap (tsr)
    VALUES ('[2023-10-22 03:00, 2023-10-22 04:00)')
ON CONFLICT
    DO NOTHING
RETURNING
    *;

-- avoid only adjacent conflicts
INSERT INTO test_gist_lap (tsr)
    VALUES ('[2023-10-22 02:30, 2023-10-22 04:00)')
ON CONFLICT ON CONSTRAINT tsr_no_adjacent
    DO NOTHING
RETURNING
    *;
/*
ERROR:  conflicting key value violates exclusion constraint "tsr_no_overlap"
DETAIL:  Key (tsr)=(["2023-10-22 02:30:00","2023-10-22 04:00:00")) conflicts with existing key (tsr)=(["2023-10-22 02:00:00","2023-10-22 03:00:00")).
 */

-- exclusion constraints not supported as arbiter for ON CONFLICT DO UPDATE
-- misleading error msg!
INSERT INTO test_gist_lap (tsr)
    VALUES ('[2023-10-22 02:00, 2023-10-22 03:00)')
ON CONFLICT (tsr)
    DO UPDATE SET
        tbl_id = test_gist_lap.tbl_id * -1
    RETURNING
        *;
-- ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

-- exclusion constraints not supported as arbiter for ON CONFLICT DO UPDATE
-- bringing the actual showstopper to light
INSERT INTO test_gist_lap (tsr)
    VALUES ('[2023-10-22 02:00, 2023-10-22 03:00)')
ON CONFLICT ON CONSTRAINT tsr_no_overlap
    DO UPDATE SET
        tbl_id = test_gist_lap.tbl_id * -1
    RETURNING
        *;
-- ERROR:  ON CONFLICT DO UPDATE not supported with exclusion constraints