Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

Re: [OT] SQL "on duplicate key" custom action

by Corion (Pope)
on Oct 21, 2019 at 07:36 UTC ( #11107748=note: print w/replies, xml ) Need Help??

in reply to [OT] SQL "on duplicate key" custom action

I think the "best" approach (if that is supported by your DBMS) are ON UPDATE triggers, or if that import is a one time thing only, creating a unique key across all columns:

create or replace function skip_insert() returns trigger language plpg +sql as $$ begin return null; end $$; create or replace function raise_exception() returns trigger language +plpgsql as $$ begin RAISE EXCEPTION 'UPDATE changing row values not allowed'; end $$; CREATE TRIGGER mytrigger ON records INSTEAD OF UPDATE WHEN old.PriKey = new.PriKey and old.Foo=new.Foo and old.Bar=new.B +ar FOR EACH ROW EXECUTE PROCEDURE skip_insert() CREATE TRIGGER mytrigger ON records INSTEAD OF UPDATE WHEN old.PriKey = new.PriKey and (old.Foo!=new.Foo or old.Bar!=new +.Bar) FOR EACH ROW EXECUTE PROCEDURE raise_exception()

... but this approach does not handle NULL values.

Replies are listed 'Best First'.
Re^2: [OT] SQL "on duplicate key" custom action
by erix (Parson) on Oct 21, 2019 at 16:25 UTC

    Do you perhaps have a tested version? I can't get this to run.

    The trigger functions are all right, it seems, but I don't see how to create the triggers themselves onto the table 'records' so that they react in the desired way. (For one thing, you can't do an 'INSTEAD OF UPDATE' on a table, and when you use a BEFORE INSERT or an AFTER INSERT trigger, this old. and new. business is not going to work.)

      No, sorry - I don't have a tested version, and I'm not on the machine where I did the searches.

      I worked from the Postgres 12 documentation and some Stackoverflow response, but I don't find them now :( I think it should be a before update trigger and the statement would be insert or update maybe. But again, I didn't test any of this, sorry.

Re^2: [OT] SQL "on duplicate key" custom action
by haukex (Chancellor) on Oct 21, 2019 at 15:00 UTC

    Thanks very much! It's not just a single import, it'll be a large import roughly once a month. So I understand you'd suggest a trigger over a unique key, may I ask why? (Performance?)

      An index across the complete table will eat up lots of disk space, and also will make each insert slow(er). Think of an index as (somewhat structured) hash keys, where you can also quickly look up strings starting with a specific string.

      If your table is largely read-only and you have the disk space to spare, the index will speed up queries maybe a bit more than an index only on the primary keys, because the DB can satisfy the query completely from the index without hitting the table storage.

      The triggers will slow down all UPDATE statements but leave SELECT untouched and also will not use additional storage.

        Ok, thank you! I do think that since the database will probably grow relatively large, I don't want to be too wasteful with the space, and slightly slower imports are probably fine. I'll try to get the trigger working and maybe I'll do a comparison with the unique key method too.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://11107748]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (7)
As of 2019-11-21 08:16 GMT
Find Nodes?
    Voting Booth?
    Strict and warnings: which comes first?

    Results (104 votes). Check out past polls.