I'm trying to create a table with a
path column. This table should only allow inserting a new path if the new path is neither an ancestor nor a descendant of any already inserted path. i.e. with
'/foo/bar' already in the table:
Essentially, these are absolute and fully-resolved (no
/./) unix path to some filesystem directories (they are relative in my precise use case but all will have the same origin so they can be safely compared with the same logic as one would use for absolute paths).
My solution was to try an
exclude constraint using the
starts_with) operator (with proper r-trimming and appending of a path delimiter to avoid false-positives, like
CREATE TABLE location ( id UUID NOT NULL, path VARCHAR NOT NULL, EXCLUDE USING btree ((rtrim(path, '/') || '/') WITH ^@) )
which gets me this error:
Only commutative operators can be used in exclusion constraints.
I know the operator is not commutative, but I (wrongly) expected postgres would see this and do the check both ways (thus excluding both ancestors and descendants).
I've tried looking up alternatives but haven't found one. How can I enforce this constraint at the db level? I really don't want to do this at the application level, as this would force me to deal with race conditions for something that really should be dealt with on the db side (data integrity concerns).
The only other solution I can see is using an insert trigger that select all the rows in the table and throws if any of them matches my condition. This seems inefficient, and a bit ugly (it would essentially be a PL/pgSQL version of that exclude constraint, thus foregoing any performance benefit of an index).
Any ideas ? Thanks in advance :)