Constraint on multiple columns?

Question

I have a table edges, which describes relations in a graph:

CREATE TABLE IF NOT EXISTS edges (
   src INT NOT NULL REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE
  ,tgt INT NOT NULL REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE
  ,rel TEXT NOT NULL
  ,rel_type INT NOT NULL
  ,PRIMARY KEY (src, tgt, rel)
  ,UNIQUE (src, tgt, rel)
  );

After inserts:

select * from edges;
 src | tgt |    rel    | rel_type 
-----+-----+-----------+----------
   1 |   2 | 5.4.2.2   |        2
   2 |   3 | 5.3.1.9   |        2
   ...
   5 |   6 | 2.7.1.2   |        1
   5 |   6 | 2.7.1.147 |        1
   6 |   2 | 5.3.1.9   |        2
   6 |   3 | 5.3.1.9   |        2
   ...

I am using rel_type to specify edge directionality (0: undirected; 1: source to target; 2: bidirectional).

Hence, inserting (3, 2, '5.3.1.9', 2) is redundant with respect to the second entry above (for example) -- which already expresses the reciprocal relationship 2 --> 3 and 3 --> 2.

How can I add a constraint that prevents the insertion of those redundant, reciprocal relations -- ideally ON CONFLICT DO NOTHING?

Basically, something like (these don't work: first due to syntax; second due to other issues):

ALTER TABLE edges ADD CONSTRAINT duplicate_rel_check CHECK ((src, tgt) <> (tgt, src) WHERE rel_type = 2);

or

CREATE UNIQUE INDEX ON edges ( greatest(src, tgt, rel_type=2), least(tgt, src, rel_type=2) );

Answer 1

A UNIQUE, multicolumn, partial expression index should do the trick:

CREATE UNIQUE INDEX ON edges (LEAST(src, tgt), GREATEST(src, tgt), rel)
WHERE rel_type = 2;

Works with INSERT ... ON CONFLICT DO NOTHING.

Related:

• Unique postgres constraint for permutations across multiple columns
• How to create a postgres table with unique combined primary key?
• PostgreSQL multi-column unique constraint and NULL values

Aside: If those are IP addresses, consider the data type cidr or ip4 from the additional module ip4r for column rel.