I have two tables in PostGIS: afrivs, which is a polyline of various rivers, and weirs2 which is an empty polyline layer where potential dam sites will be added.
Obviously dams have to cross a river to work so I would like to add a constraint to enforce this. Following the examples here https://spatialdbadvisor.com/postgis_tips_tricks/127/how-to-apply-spatial-constraints-to-postgis-tables I tried adding the following constraint to check that any new dam polyline crosses a river polyline:
ALTER TABLE weirs2 ADD CONSTRAINT river_intersect
CHECK (ST_Intersects(afrivs.geom,weirs2.geom)=true);
However this gives me the following error message.
ERROR: missing FROM-clause entry for table "afrivs"
I also tried framing it as a SELECT query to include a FROM clause then get
syntax error at or near "SELECT".
Based on this https://dba.stackexchange.com/questions/149466/missing-from-clause-entry-with-constraint-on-value-from-type-in-postgresql, I tried putting the table names in () but then get:
ERROR: column "afrivs" does not exist
What am I missing?
Using POSTGIS 2.4.4 r16526 on PostgreSQL 10.6.
Best Answer
It worked! Thanks ThingumaBob.
For reference the code I used:
For bonus points:
Do I need
SELECT EXISTS
or wouldSELECT 1
also work?is there any way of having the
afrivs
table as an argument rather than hard-coding it into the function?WHERE tb.geom && gm
line - is this just to check that they are both non-null?Cheers,