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.

Its an UPSERT Scenario. read this https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql/30118648#30118648 — Akhilesh Mishra, May 20 '21 at 08:03
you can create a unique index on table for end,start and use on conflict in insert (https://www.postgresql.org/docs/13/sql-insert.html) — Philippe, May 20 '21 at 08:03

Jim Jones · Accepted Answer · 2021-05-21T10:28:55.603

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

score 0 · Answer 2 · answered May 20 '21 at 11:48

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.

@Maas . . . This is the better solution, because it uses the database to ensure uniqueness. — Gordon Linoff, May 21 '21 at 14:18
Nice answer. The `create unique index` syntax with the underscore between `start` and `end` is quite strange. Should'nt it be a `,` instead ? Maybe `(start, end)` would make a good PRIMARY KEY ? — 0x0fba, Nov 15 '22 at 13:46

preventing insert on duplicate values Postgres

2 Answers2