You can do this by creating a constraint against a user-defined function that checks for overlaps in your table. Here's an example:
CREATE TABLE my_data (
id int PRIMARY KEY,
geom geometry
);
CREATE INDEX ON my_data USING gist(geom);
Now that the table exists, we can create a function to check for overlaps in it. I like to use ST_Relate for this, since it can check for what most of us mean by "overlaps". (ST_Overlaps reports false when one polygon is contained within another). Because we've created a spatial index, the insert check to insert n records will be O(n log n), not O(n^2). I've included an id check too, so that we won't check a new version of a row against an old version of a row if we run an UPDATE query.
CREATE FUNCTION no_overlaps_in_my_data(id int, g geometry)
RETURNS boolean AS $$
SELECT NOT EXISTS (
SELECT 1 FROM my_data
WHERE my_data.id != id
AND my_data.geom && g
AND ST_Relate(my_data.geom, g, '2********'));
$$ LANGUAGE sql;
Now, we can create a constraint using this function:
ALTER TABLE my_data ADD CONSTRAINT no_overlaps CHECK (no_overlaps_in_my_data(id, geom));
And verify that it works:
INSERT INTO my_data VALUES (1, ST_Buffer(ST_MakePoint(1, 1), 1));
-- OK
INSERT INTO my_data VALUES (2, ST_Buffer(ST_MakePoint(3, 1), 1));
-- OK
INSERT INTO my_data VALUES (3, ST_Buffer(ST_MakePoint(2, 1), 1));
-- ERROR: new row for relation "my_data" violates check constraint "no_overlaps"
