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.

How to convert dmg file to an iso image

I found this out a while ago when looking for info on reading .DMG files on Windows or Linux boxes. I found out it was not possible, and I wasn't too happy. You see, my iMac has no CD-R drive, just a CD-ROM. This tip creates ISO images from DMG images, so they can be burned elsewhere. To convert the file to an ISO image, type the following command at your terminal window:

hdiutil convert /path/to/filename.dmg -format UDTO -o /path/to/savefile.iso

Replace /path/to/filename.dmg with the path and name of the existing .DMG file, and replace /path/to/savefile.iso with the desired path and name for the converted image.

This then creates an ISO image burnable in Nero on Windows, or pretty much anything on Windows that will burn ISOs and same with Linux. I just converted a DMG image as a test, and it took a while -- it only converted at about 1MByte per secoond, but I only have a 333Mhz imac G3, so speed wise, it may be good.