October 30th, 2008 by depesz | Tags: , , , | 2 comments »
Did it help? If yes - maybe you can help me?

Let's imagine following situation:

create table test (id int4 primary key, priority int4);
insert into test (id)
    select distinct (random() * 100000000)::int4 from generate_series(1,1000);

Table test will now contain some (up to 1000) records, with random ids.

Now, we want to update first 3 records (ordered by id) to have following values in priority:

  1. 10000
  2. 5000
  3. 1000

Now, I could of course do something like this:

# select id from test order by id asc limit 3;

And then issue multiple updates (3 in this example), but there has to be a way to do it in less roundtrips, and additional logic on application side.

Well, I found this way:

First, I need to get first three rows, which is simple:

select id from test order by id asc limit 3.

Then I will have to somehow “attach" to them the priority values. I found a relatively simple way using temporary sequence:

create temp sequence temp_seq;
select id, (array[10000,5000,1000])[nextval('temp_seq')] from (
    select id from test order by id asc limit 3
) as x;

Whoa, what does it do?

It's rather simple: it gets first 3 records using the query we had before, then (using subselect to preserve ordering) gets next value for given row.

This element:

(array[10000,5000,1000])[nextval('temp_seq')]

Generates array with 3 items – values 10000, 5000 and 1000, and then chooses only one of the values – the one with index given by nextval().

Since nextval() gives values in order: 1, 2, 3, …, and array indexes start with 1 – it's perfect match.

Now, all that is left is to update the table, but since we used first 3 values from seq, I will just recreate it, and issue the update:

drop sequence temp_seq;
create temp sequence temp_seq;
update test as t SET priority = q.priority FROM (
select id, (array[10000,5000,1000])[nextval('temp_seq')] as priority from (
    select id from test order by id asc limit 3
) as x) as q where t.id = q.id;

Final effect:

# select * from test order by id asc limit 4;
   id   | priority
--------+----------
 187272 |    10000
 219755 |     5000
 244572 |     1000
 457438 |   [null]
(4 rows)

Done. The code is not really pretty, but it does the job, and it requires only 2 commands to be send:

  • creation of temporary sequence
  • update itself
  1. 2 comments

  2. Oct 30, 2008

    same without arrays:

    UPDATE test
    SET priority = labels.label
    FROM (select id, nextval(‘tmpseq’) as rank from test order by id limit 3) as ordered,
    (VALUES(1,10000),(2,5000),(3,1000)) as labels (rank,label)
    WHERE ordered.rank = labels.rank
    AND temp.id = ordered.id;

  3. Oct 30, 2008

    @Filip:
    array notation seems to be more cost (character-wise) effective than values (). Also – please note that your code assumes that this query:
    select id, nextval(’tmpseq’) as rank from test order by id limit 3
    will not *order* rows, but will get them already ordered (presumably from index scan).

    of course – it will work just fine, i’m just nitpicking 🙂

Sorry, comments for this post are disabled.