How to create a spatial constraint in a table based on a polygon?

I've got a table that will collect a few measurements that must be inside of a certain area. I'm aware of triggers to create this validation but I'm looking for something easier, since the area I need to validate my records against is a single polygon.

Here is a simplified version of my table:

CREATE TABLE measurements (
  m_time timestamp,
  temperature int,
  geom geometry (point,4326)
);

All inserted records must inside of the following polygon

POLYGON((-35.32 -7.96,-35.27 -7.96,-35.27 -8.01,-35.32 -8.01,-35.32 -7.96))

A few sample data:

INSERT INTO measurements VALUES 
  ('2020-06-26 13:30:08',31,'SRID=4326;POINT(-35.29 -7.98)'),
  ('2020-06-26 12:27:42',34,'SRID=4326;POINT(-35.29 -7.99)'),
  ('2020-06-26 12:27:42',34,'SRID=4326;POINT(-35.35 -8.0)'); -- this point lies outside of the polygon

How to validate records prior to insert without using triggers?

Solution

Welcome to SO.

The easiest way to do so is to add a CHECK constraint to your table using the spatial function ST_Contains with this polygon of yours, e.g.:

ALTER TABLE measurements 
  ADD CONSTRAINT check_location 
  CHECK(
    ST_Contains('SRID=4326;POLYGON((-35.32 -7.96,-35.27 -7.96,-35.27 -8.01,-35.32 -8.01,-35.32 -7.96))',geom));

Inserting the first two records wil work:

INSERT INTO measurements VALUES 
  ('2020-06-26 13:30:08',31,'SRID=4326;POINT(-35.29 -7.98)'),
  ('2020-06-26 12:27:42',34,'SRID=4326;POINT(-35.29 -7.99)');

INSERT 0 2

But it fails if the given point lies outside of the polygon:

INSERT INTO measurements VALUES 
  ('2020-06-26 12:27:42',34,'SRID=4326;POINT(-35.35 -8.0)'); 

ERROR:  new row for relation "measurements" violates check constraint "check_location"
DETAIL:  Failing row contains (2020-06-26 12:27:42, 34, 0101000020E6100000CDCCCCCCCCAC41C000000000000020C0).

Further reading: PostgreSQL CHECK Constraint