[SOLVED] preventing insert on duplicate values Postgres

preventing insert on duplicate values Postgres

I am building a distance matrix with this SQL statement:

INSERT INTO tabel (start, end, agg_cost)

SELECT * FROM pgr_dijkstraCostMatrix(
    'SELECT id, source, target, cost, reverse_cost FROM edges',
    (SELECT array_agg(id) FROM vertices 
     WHERE (vertices.eout =1)  OR (vertices.eout >15) ) 
);

That works fine.

How do I prevent INSERT when the combination of 'start' and 'end' already exist in table? I am looking for another WHERE clause, but can not find out where to put it.

Solution

In case an UPSERT isn't an option, you could try to include this condition in a SELECT, e.g.

INSERT INTO tabel (start, end, agg_cost)
SELECT * FROM pgr_dijkstraCostMatrix(
    'SELECT id, source, target, cost, reverse_cost FROM edges',
    (SELECT array_agg(id) FROM vertices 
     WHERE (vertices.eout =1)  OR (vertices.eout >15) ) 
) q WHERE NOT EXISTS (
            SELECT 1 FROM tabel t 
            WHERE t.start = q.start AND
                  t.end = q.end);

Demo: db<>fiddle