Duplicaten in PostgreSQL
Uit Yapf
Inhoud |
Heeft PostgreSQL ook 'ON DUPLICATE'?
Van MySQL gebruikers krijg ik af en toe de vraag of PostgreSQL ook een 'ON DUPLICATE' functie heeft. Met die functie kun je in MySQL aangeven dat er een alternatieve actie gedaan moet worden wanneer een insert query een dubbele waarde voor een key invoert.
Mijn eerste reactie is dan "Waarom zit je een dubbele waarde in te voeren?"
In MySQL schijnt dit veel gebruikt te worden om bijvoorbeeld hits van een webserver te tellen. De tabel bevat dan een kolom voor de URL die geteld moet worden met een unique key er op, en een INT kolom om de telling in bij te houden. Het beleid is dan om altijd te INSERTen en wanneer de waarde voor de URL al bestond via de 'ON DUPLICATE' een UPDATE query op te starten.
PostgreSQL heeft geen 'ON DUPLICATE', maar wel triggers en rules, waarmee je veel flexibelere oplossingen kunt maken.
De test tabel
Deze tabel kun je gebruiken om deze rule en trigger mee te testen:
CREATE TABLE users(
userid SERIAL,username VARCHAR(255),
spampoints INTEGER
);
Er zit op deze tabel geen unique contraint. Dat is om aan te tonen dat de rules en triggers inderdaad werken. In productie zou je er natuurlijk wel een unique op zetten.
Oplossingen
De Rule oplossing
Met een RULE is de ON DUPLICATE zo gepiept:
CREATE RULEusers_rule
ASON INSERT TO
users
WHERE(EXISTS ( SELECT 1 FROM users
WHERE users.username= new.username))
DO INSTEAD
(UPDATE users
SETspampoints = users.spampoints + 1
WHEREusers.username = new.username);
De trigger oplossing
Als het perse nodig moet zijn dat je meerdere dubbelen tegelijk in kunt voeren dan is er altijd nog de goede oude trigger:
CREATE OR REPLACE
FUNCTION users_update_trigger() RETURNS
TRIGGERAS$body$
DECLARE
junk integer;
BEGIN
SELECT INTO junk 1 FROM
users WHERE username=NEW.username;
IF FOUND THENUPDATE users SET spampoints =
spampoints+1 WHERE username=NEW.username ;
RETURN NULL;
ELSE
NEW.spampoints=1;
RETURN NEW;END IF;END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY
INVOKER;
Let op dat de trigger hier NULL teruggeeft om te voorkomen dat de INSERT alsnog wordt gedaan. Let ook op dat ik de waarde van spampoints hier op 1 forceer. Ik ga er stiekum vanuit dat je niet ook nog eens een waarde voor spampoints mee gaat geven als je een dubbel record in zit te voeren. (oh wat heerlijk tegendraads dit allemaal...)
En de trigger:
CREATE TRIGGER users_tr
BEFORE
INSERTONusers
FOR EACHROW
EXECUTE PROCEDURE
users_update_trigger();