TLDR: Is it possible to add a unique constraint to a column in a Postgres table that will use a custom comparison operator or function to compare the newly inserted or modified column with the rest of the table to guarantee uniqueness.
For my project, I have a table of network IP ranges, and I am using the iprange type from github.com/RhodiumToad/ip4r postgres extension. That extension supports storing arbitrary network IP ranges, which can be of any size and need fall precisely on CIDR boundaries.
In my database design, I want there to be an error if the application attempts to insert a row describing an iprange that overlaps with any existing iprange in the table. The ip4r extension provides the && operator that can test if two IP ranges overlap.
For example:
SELECT * FROM net_table WHERE network && '192.168.1.3-192.168.1.7'::iprange
Is there a way to express the restriction as a unique index or constraint on my table somehow? Or do I have to do the check in the application before each insert or update ?
