select * from depesz;

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