PostgreSQL, check for duplicate before insert

Question

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 ?

Isn't it easier to check on duplicate values using a unique index on test_id ? — Conffusion
– Conffusion, Commented Jul 15, 2019 at 8:58
Create a unique index on test_id then use insert ... on conflict (test_id) do nothing - no trigger required. And it will even work with concurrent inserts of the same value (which the trigger will not be able to detect) — user330315
– user330315, Commented Jul 15, 2019 at 8:58
I do allow test_id to be NULL, which should allow insert - would this still work? — Alfred Balle
– Alfred Balle, Commented Jul 15, 2019 at 9:06
@Alfred Valid remark but apparently it should still work: see stackoverflow.com/questions/23449207/…) — Conffusion
– Conffusion, Commented Jul 15, 2019 at 12:21

madflow · Accepted Answer · 2019-07-15 09:01:43Z

4

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

answered Jul 15, 2019 at 9:01

madflow

8,7013 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Alfred Balle Over a year ago

I do allow test_id to be NULL, which should allow insert - would this still work?

madflow Over a year ago

In general, a unique constraint is violated if there is more than one row in the table where the values of all of the columns included in the constraint are equal. However, two null values are never considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases might not follow this rule. So be careful when developing applications that are intended to be portable.

madflow Over a year ago

If test_id ist the only column in the unique constraint - Postgresql will allow NULL values.

Alfred Balle Over a year ago

I'll accept this as it was the first solution. THank you all.

Nix Over a year ago

@AlfredBalle what if you already have duplicate data in your column an you want the new entries to now unique without deleteing the old data? I think a trigger would apply there

Gordon Linoff · Accepted Answer · 2019-07-15 11:10:00Z

1

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;

answered Jul 15, 2019 at 11:10

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Alfred Balle Over a year ago

Would ON CONFLICT (test_id) DO UPDATE SET test_id = test_id also work as sort of upsert ? As I'm getting error in my application with DO NOTHING because it exepcts to return id of inserted row.

Collectives™ on Stack Overflow

PostgreSQL, check for duplicate before insert

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related