Eu tenho uma tabela edges
, que descreve as relações em um gráfico:
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)
);
Após inserções:
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
...
Estou usando rel_type
para especificar a direcionalidade da borda ( 0
: undirected; 1
: source to target; 2
: bidirecional).
Portanto, inserir (3, 2, '5.3.1.9', 2)
é redundante em relação à segunda entrada acima (por exemplo) -- que já expressa a relação recíproca 2 --> 3
e 3 --> 2
.
Como posso adicionar uma restrição que impeça a inserção dessas relações redundantes e recíprocas - idealmente ON CONFLICT DO NOTHING
?
Basicamente, algo como (estes não funcionam: primeiro devido à sintaxe; segundo devido a outros problemas):
ALTER TABLE edges ADD CONSTRAINT duplicate_rel_check CHECK ((src, tgt) <> (tgt, src) WHERE rel_type = 2);
ou
CREATE UNIQUE INDEX ON edges ( greatest(src, tgt, rel_type=2), least(tgt, src, rel_type=2) );
Um índice de expressão parcial
UNIQUE
, multicolunas deve fazer o truque:Funciona com
INSERT ... ON CONFLICT DO NOTHING
.Relacionado:
À parte: se forem endereços IP, considere o tipo de dados
cidr
ouip4
do módulo adicional ip4r para a colunarel
.