CODEWITHSUNDEEP
sqlpostgresql
Alfred Balle
Alfred Balle

Reputation: 1195

PostgreSQL, check for duplicate before insert

I'm running PostgreSQL 10 and have several BEFORE INSERT OR UPDATE and AFTER INSERT OR UPDATE on my table tests.

I want to have another trigger BEFORE INSERT OR UPDATE which should check for potential duplicate row.

I've made this:

CREATE OR REPLACE FUNCTION set_check_dublicate_on_test() RETURNS trigger AS $$
    BEGIN
        IF EXISTS (SELECT 1 FROM tests WHERE test_id = NEW.test_id) THEN
            RETURN NULL;
        ELSE
            RETURN NEW;
        END IF;

    END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS set_check_dublicate_on_test ON tests;
CREATE TRIGGER set_check_dublicate_on_test BEFORE INSERT OR UPDATE ON tests
    FOR EACH ROW EXECUTE PROCEDURE set_check_dublicate_on_test();

But I'm not sure if it will conflict with other triggers or it will fullfill the goal, and the triggers simply will be ignored if this returns NULL ?

Upvotes: 2

Views: 7520

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269813

Let the database manage uniqueness! This ensures data integrity for both inserts and updates.

This is quite simple:

alter table test add constraint unq_test_test_id unique (test_id);

If you insert rows one at a time, then this is fine. The insert (or update) will fail.

If you want to insert multiple rows and allow non-duplicate inserts to go in, then us on conflict:

insert into test ( . . . )
    select . ..
    from . . .
    on conflict on constraint unq_test_test_id do nothing;

Upvotes: 1

madflow
madflow

Reputation: 8490

Firstly - I believe that if you want to have a unique field in your table - then it is the easiest to mark it as such:

https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS

If an attempted insert on such a field should not raise an error, then there is a the ON CONFLICT keyword:

https://www.postgresql.org/docs/current/sql-insert.html

Especially: ON CONFLICT DO NOTHING

Upvotes: 4

Related Questions