Wednesday 1 June 2011

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');

No comments:

Post a Comment