Bidirectional self join table

Question

Consider:

CREATE TABLE object (  
    id integer NOT NULL DEFAULT nextval('object_seq'),  
    ...,  
    CONSTRAINT object_pk PRIMARY KEY (id)  
);

CREATE TABLE pairing (  
    object1 integer NOT NULL,  
    object2 integer NOT NULL,  
    CONSTRAINT pairing_pk PRIMARY KEY (object1, object2)
);

ALTER TABLE pairing ADD CONSTRAINT object1_fk FOREIGN KEY (object1) REFERENCES object (id);
ALTER TABLE pairing ADD CONSTRAINT object2_fk FOREIGN KEY (object2) REFERENCES object (id);

You have a table object with one column id and some other unimportant columns, and another table pairing which is joining the table object with itself, meaning two columns object1 and object2, both foreign keys of object.id.

These two columns are semantically identical. Nothing like an adjacency list, you know, with one column being parent and the other being child. It means the columns are reversible, and because of that, you have the same data twice because the order of the pairing inside the table pairing is irrelevant.

Exemple:

object1-object2
1-2
1-3
1-4
2-1
3-1
4-1

1-2 is identical to 2-1, 1-3 to 3-1, etc.
You can query in any direction you want because what you want to know is with which object, an object is connected to. You can query either

• SELECT object2 FROM pairing WHERE object1 IN (SELECT id FROM object WHERE <some_criterion>);
• SELECT object1 FROM pairing WHERE object2 IN (SELECT id FROM object WHERE <some_criterion>);

Given that, is there another way to do that without having half the table being "duplicates", or just another more efficient way?

I could cut the table in half but it would force me to query in both direction at the same time and I fear 1/ it will degrade the performance given speed is of the essence 2/ I will have to make multiple self join against that very table and doing them in both directions, it might be doable but at best I feel it will be awful.
Here is an example of such query where I want to know if one object is paired with other objects, at the same time:

SELECT object1 FROM pairing p1
JOIN pairing p2 ON p1.object1 = p2.object1
JOIN pairing p3 ON p1.object1 = p3.object1
...
WHERE p1.object2 IN (SELECT id FROM object WHERE <some_criterion>)
AND p2.object2 IN (SELECT id FROM object WHERE <some_criterion>)
AND p3.object2 IN (SELECT id FROM object WHERE <some_criterion>)

Same with a recursive CTE or just joining a table after the other like JOIN ON p1.object2 = p2.object1. I don't feel like you can split a query in two and union the results later because when you use this table twice, it "spins it into a direction" and you can't use a separate query to see if it has the rest of the results in the other direction. Not sure I am clear there.

Another solution could be to collate in the FROM clause the table with itself in the other direction, recreating the table with "duplicates" but given this virtual table will be used all the time, it will be cached so wouldn't it be better to just have it this way from the beginning and not care about the double size?

Answer 1

Declare a constraint on Pairing that object1 must be less (or equal if self-pairing is permitted) than object2.

ALTER TABLE pairing
  ADD CONSTRAINT pairings
  CHECK (object1 < object2);

For symmetric reads union two results sets which are guaranteed to be disjoint.

select object2 as object
from pairing
where object1 = <given value>
union all
select object1
from pairing
where object2 = <given value>

Again, may need some tweaking if self-pairing is permitted. The constraint ensures no row can be in both halves of this query. UNION ALL avoids redundant de-duplication. With an index on each column access will be efficient.