By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE outerwear ( outerwear_id integer primary key);
CREATE TABLE outerwear_outerwear_join (
a_outerwear_id integer REFERENCES outerwear,
b_outerwear_id integer REFERENCES outerwear,
PRIMARY KEY(a_outerwear_id, b_outerwear_id)
);
create unique index unq_test_a_b on outerwear_outerwear_join ( least(a_outerwear_id, b_outerwear_id), greatest(a_outerwear_id, b_outerwear_id));
insert into outerwear(outerwear_id) values(1);
1 rows affected
insert into outerwear(outerwear_id) values(2);
1 rows affected
INSERT INTO outerwear_outerwear_join(a_outerwear_id, b_outerwear_id) VALUES ( 1,2);
1 rows affected
INSERT INTO outerwear_outerwear_join(a_outerwear_id, b_outerwear_id) VALUES ( 2,1);
ERROR: duplicate key value violates unique constraint "unq_test_a_b"
DETAIL: Key ((LEAST(a_outerwear_id, b_outerwear_id)), (GREATEST(a_outerwear_id, b_outerwear_id)))=(1, 2) already exists.
select * from outerwear_outerwear_join;
| a_outerwear_id | b_outerwear_id |
|---|---|
| 1 | 2 |