Ordered updates with Postgres

When updating a postgres table you sometimes want the update to happen in a specific order. For example I found myself in a situation when I wanted to assign new sequential ids to records in the alphabetical order given by a text string column.

With postgres 8.4 the solution using an updateable, ordered view didn't work (anymore?). After experimenting a little I found that clustering a table according to the desired order is a simple solution that works exactly as hoped for. Clustering changes the actual order of the table data instead of only adding a new index. And apparently postgres uses this native order for updates.

CREATE TABLE idupd (id int, name varchar(128));

CREATE INDEX idupd_idx ON idupd (name);

CLUSTER idupd USING idupd_idx;

CREATE SEQUENCE idupd_seq;

SELECT setval('idupd_seq', 100);

UPDATE idupd set id=nextval('idupd_seq');