PostgreSQL: UPDATE a table using ORDER BY

MySQL is great in that it really lets you get away with a lot. Something I’ve found frequently valuable is the ability to update a table in a particular order, such as when you want a column to have a particular numerical sequence, and you want that sequence to share the same order as another field. Let me give you an example.

In many of our tables we allow site administrators to sort elements (rows) according to a certain priority (in this case, 1 being the highest priority). For one particular client, they wanted the ability to sort their list of team members according to priority. This allowed them to order their employees according to any criteria they desire (say, in this case, seniority, or rank). However, recently they asked us to sort their employee table according to name. Under MySQL this would be easy. You could run the following:

SET @p=1;
UPDATE team SET priority = (@p:=@p+1) ORDER BY name ASC;

Piece of cake: each employee is given a certain priority according to name. However, under PostgreSQL, this is much more challenging, and I couldn’t find any easy solution on the web. It turns out, the solution requires the use of views, rules, and sequences, things that are foreign to most MySQL users and all but the more advanced PostgreSQL users. Here are the following steps:

1) You must first create a view on the table that you want to update in the particular order that you want to update by:

CREATE VIEW view_team AS SELECT * FROM team ORDER BY name ASC;

2) To run the update on the view you just created, you have to create a rule telling the view how to interpret UPDATE queries you’re going to run on it:

CREATE RULE rule_team AS ON UPDATE TO view_team DO INSTEAD UPDATE team SET priority = NEW.priority WHERE id = NEW.id;

This specifies that when you try to run an update on the view_team view, it will apply those changes to the team table where we want those changes to show up. You can change more than one field in this rule by adding more fields to the UPDATE part of that query, but since we are only applying changes to the priority field, this will suffice.

3) Create a sequence. This will update each row in sequence, and serves as an alternative to using user-defined variables, as we did in MySQL:

CREATE SEQUENCE team_priority_seq;

4) Update the view:

UPDATE view_team SET priority = nextval('team_priority_seq');

5) Now unless you want to keep the view, rule, and sequence around, you can drop them:

DROP SEQUENCE team_priority_seq;
DROP RULE rule_team ON view_team;
DROP VIEW view_team;

Hopefully if you followed those steps, your table should be sorted in exactly the order you want. Here is everything all at once:

CREATE VIEW view_team AS SELECT * FROM team ORDER BY name;
CREATE RULE rule_team AS ON UPDATE TO view_team DO INSTEAD
UPDATE team SET
priority = NEW.priority
WHERE id = NEW.id;
CREATE SEQUENCE team_priority_seq;
UPDATE view_team SET priority = nextval('team_priority_seq');
DROP SEQUENCE team_priority_seq;
DROP RULE rule_team ON view_team;
DROP VIEW view_team;

Cameron

Tags: , , , ,

6 Responses to “PostgreSQL: UPDATE a table using ORDER BY”

  1. Christian Says:

    You saved my day!
    Thank you for this useful information.

  2. jasper Says:

    Hi,

    I tried this solution over and over again, i just cant get it to work. If I do a SELECT query on my newly created view, it shows all the data ordered like it supposed to be. But when executing an update against the view (with the rule in place) it just seems to ignore the order in the view. So i end up with exactly the same result as i would have executing an update query against the normal table?
    I am using postgres 8.4.2, not sure if that changes anything.

    Any help would be appreciated!

  3. Dexel Says:

    order by ignore!

  4. Mike T Says:

    Another good strategy is to use a self-sub-select, as described http://postgresql.1045698.n5.nabble.com/UPDATE-FROM-will-ORDER-BY-not-respected-td1917581.html

    Sorta like:

    UPDATE impt_table
    SET id = newid
    FROM
    (
    SELECT foo.seq,
    nextval(”id_seq”) AS newid
    (SELECT seq
    FROM impt_table
    WHERE id IS NULL
    ORDER BY seq
    ) AS foo
    ) ASempty_ids
    WHERE
    impt_table.seq = empty_ids.seq
    AND impt_table.id IS NULL;

  5. Project management and web design | Project management and time tracking blog for web designers and small business :: the Intervals Blog by Pelago Says:

    Kramer auto Pingback[...] Pelago Blog :: web development links and random tidbits from the creative minds of Team Pelago Says: December 5th, 2008 at 9:55 am [...]

  6. Developer Blog: Ordered updates with Postgres Says:

    Kramer auto Pingback[...] 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 [...]

Leave a Reply