REPLACE in PostgreSQL

I've recently made the switch from MySQL to PostgreSQL for some of my more complicated database projects, such as the ad matching discussed in my last post. PostgreSQL has a much richer feature set (although MySQL 5 is catching up). One feature I couldn't find in PostgreSQL however was the REPLACE extension offered by MySQL where rows that occur in duplicated primary keys replace the existing rows instead of give errors. Fortunately, PostgreSQL's advanced rules system allows the creation of an equivalent feature.

For the sake of this example we'll use a very simple table called 'map' with a two fields: 'key' and 'value'. Not surprisingly, key is the primary key.

Let's further assume that every insert into this table should actually be a replace. Then all we need is this simple statement and we're done:

-- Merge rule
CREATE OR REPLACE RULE merge_btv_lookup_weather_locations AS
ON INSERT TO btv.lookup_weather_locations
WHERE (EXISTS ( SELECT 1
FROM btv.lookup_weather_locations
WHERE lookup_weather_locations.code = new.code)) DO INSTEAD UPDATE btv.lookup_weather_locations SET name = new.name, type = new.type, active = new.active
WHERE lookup_weather_locations.code = new.code;

That's it! So how does it work? First, we create a rule to be processed whenever a row is inserted into the map table. Then, we use the subquery EXISTS(SELECT 1 FROM map WHERE key=NEW.key) to test for a row on the same primary key. If there isn't a row, the query proceeds as a normal INSERT. But if there is a row, the query is converted into an UPDATE that sets the value instead of trying to insert it. The 'NEW.var' means whatever values we were trying to INSERT.

This is why I'm starting to become a PostgreSQL fan.