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:
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.
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;
# 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
2 thoughts on “Tips N’ Tricks – setting field based on order”
same without arrays:
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;
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 🙂
Comments are closed.