preventing insert on duplicate values Postgres

Question

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.

Answer 1

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

Answer 2

How do I prevent INSERT when the combination of 'start' and 'end' already exist in table?

Start by creating a unique index or constraint and then use on conflict:

create unique index unq_tabel_start_end on tabel(start_end);

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
             )
                              ) 
    ON CONFLICT (start, end) DO NOTHING;

You can also update the existing row if you like, rather than ignoring the new data.