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

Here's the solution I've ended up with, based on your suggestion:

CREATE FUNCTION skip_update() RETURNS trigger AS $$
    BEGIN
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION fail_update() RETURNS trigger AS $$
    BEGIN
        RAISE EXCEPTION 'can''t modify row values for existing primary
+ key';
    END;
$$ LANGUAGE plpgsql;
CREATE TABLE Dummy_Test (
    Timestamp TIMESTAMP WITH TIME ZONE PRIMARY KEY,
    Foo TEXT,
    Bar TEXT
);
CREATE TRIGGER Dummy_Test_dupe BEFORE UPDATE ON Dummy_Test FOR EACH RO
+W
    WHEN ( OLD.Timestamp IS NOT DISTINCT FROM NEW.Timestamp AND OLD.Fo
+o IS NOT DISTINCT FROM NEW.Foo AND OLD.Bar IS NOT DISTINCT FROM NEW.B
+ar )
    EXECUTE FUNCTION skip_update();
CREATE TRIGGER Dummy_Test_modify BEFORE UPDATE ON Dummy_Test FOR EACH 
+ROW
    WHEN ( OLD.Foo IS DISTINCT FROM NEW.Foo OR OLD.Bar IS DISTINCT FRO
+M NEW.Bar )
    EXECUTE FUNCTION fail_update();

INSERT INTO Dummy_Test (Timestamp,Foo,Bar) VALUES ('2020-01-02 12:34',
+'Hello','World') ON CONFLICT ON CONSTRAINT Dummy_Test_pkey DO UPDATE 
+SET Foo=EXCLUDED.Foo, Bar=EXCLUDED.Bar;
INSERT INTO Dummy_Test (Timestamp,Foo,Bar) VALUES ('2020-01-02 12:34',
+'Hello','World') ON CONFLICT ON CONSTRAINT Dummy_Test_pkey DO UPDATE 
+SET Foo=EXCLUDED.Foo, Bar=EXCLUDED.Bar;
INSERT INTO Dummy_Test (Timestamp,Foo,Bar) VALUES ('2020-01-02 12:34',
+'Hello','abcde') ON CONFLICT ON CONSTRAINT Dummy_Test_pkey DO UPDATE 
+SET Foo=EXCLUDED.Foo, Bar=EXCLUDED.Bar;
[download]

The last statement will fail, which is exactly what I wanted. The function FUNCTION skip_update() and TRIGGER Dummy_Test_dupe really only prevent the UPDATE from happening, they can be omitted - I haven't yet tested whether it's more performant to have the TRIGGER or the UPDATE fire. In any case, thank you very much!

Comment on Re^2: [OT] SQL "on duplicate key" custom action Select or Download Code

Replies are listed 'Best First'.
Re^3: [OT] SQL "on duplicate key" custom action by haukex (Archbishop) on Oct 10, 2021 at 09:47 UTC
Just for the record, here's the same thing for SQLite. As above, the `TRIGGER Dummy_Test_dupe` is optional and I haven't tested the performance difference yet. use warnings; use strict; use DBI; my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:", undef, undef, { RaiseError=>1, AutoCommit=>1 } ); $dbh->do(<<'ENDSQL'); CREATE TABLE Dummy_Test ( Timestamp INTEGER PRIMARY KEY, Foo TEXT, Bar TEXT ); ENDSQL $dbh->do(<<'ENDSQL'); CREATE TRIGGER Dummy_Test_dupe BEFORE UPDATE ON Dummy_Test FOR EACH RO +W WHEN ( OLD.Timestamp IS NEW.Timestamp AND OLD.Foo IS NEW.Foo AND O +LD.Bar IS NEW.Bar ) BEGIN SELECT RAISE(IGNORE); END; ENDSQL $dbh->do(<<'ENDSQL'); CREATE TRIGGER Dummy_Test_modify BEFORE UPDATE ON Dummy_Test FOR EACH +ROW WHEN ( OLD.Foo IS NOT NEW.Foo OR OLD.Bar IS NOT NEW.Bar ) BEGIN SELECT RAISE(ABORT, "same Timestamp but different values"); +END; ENDSQL my $in = $dbh->prepare(<<'ENDSQL'); INSERT INTO Dummy_Test (Timestamp,Foo,Bar) VALUES (?,?,?) ON CONFLICT (Timestamp) DO UPDATE SET Foo=EXCLUDED.Foo, Bar=EXCLUD +ED.Bar; ENDSQL $in->execute(12345,'Hello','World'); $in->execute(12345,'Hello','World'); my $e; eval { $in->execute(12345,'Hello','abcde'); 1 } or do { $e = $@ }; if ( defined $e ) { print "Third insert failed as expected: $e" } else { die "Third insert didn't fail as expected" } [download]	[reply] [d/l] [select]
Re^3: [OT] SQL "on duplicate key" custom action (updated) by haukex (Archbishop) on Feb 24, 2020 at 17:16 UTC
The function `FUNCTION skip_update()` and `TRIGGER Dummy_Test_dupe` really only prevent the `UPDATE` from happening, they can be omitted - I haven't yet tested whether it's more performant to have the `TRIGGER` or the `UPDATE` fire. Interestingly, it made a small but consistent difference. I imported the same dataset (73613 `INSERT`s) three times into a previously populated database, and without the `TRIGGER`, i.e. with the `UPDATE`, it took an average 91.540s, and with the `TRIGGER`, i.e. preventing the `UPDATE`, it took 99.368s (each of these is only +/- about 2s), i.e. a difference of roughly 0.1ms per record. Not a big deal here, and not a rigorous test, but still interesting. Update: Importing into a previously empty database revealed only a tiny difference: overall the import was roughly 10sec faster, again `UPDATE` beating `TRIGGER` with a difference of 0.17ms per record. Again, not really significant.	[reply] [d/l] [select]