Making PostgreSQL respect the order of the inputted parameters?

Question

This question has a little history — Is there a way to make a query respect the order of the inputted parameters?

I'm new to building "specialized" queries, so I assumed that if I supply an IN clause as part of a SELECT query, it'll return results in the same order. Unfortunately that's not the case.

SELECT * FROM artists WHERE id IN (8, 1, 2, 15, 14, 3, 13, 31, 16, 5, 4, 7, 32, 9, 37)
>>> [7, 32, 3, 8, 4, 2, 31, 9, 37, 13, 16, 1, 5, 15, 14]

(Didn't include the step where I used Python to loop through the result and append the IDs to a list.)

So the question is, is there a way to make Postgres respect the ordering of the parameters given in an IN clause by returning results the same order?

Answer 1

Query results will be returned in non-deterministic order unless you specify an ORDER BY clause.

If you really want to do the query in the manner you are requesting, then you could construct such a clause. Here's an example using part of your data.

create table artists (
id integer not null primary key,
name char(1) not null);

insert into artists
values
    (8, 'a'),
    (1, 'b'), 
    (2, 'c'),
    (15, 'd'),
    (14, 'e'),
    (3,  'f'),
    (13, 'g');

select *
from artists
where id in (8, 1, 2, 15, 14, 3, 13)
order by
    id = 8 desc,
    id = 1 desc,
    id = 2 desc,
    id = 15 desc,
    id = 14 desc,
    id = 3 desc,
    id = 13 desc;

Based on this and on your other question, I think there is something wrong with your model or the way you are trying to do this. Perhaps you should post a more generic question about how to do what you are trying to do.

If you do have artists and ranking tables, you should be able to do something like this (or the equivalent through your ORM).

select
    a.*
from
    artists a,
    rankings r
where
    a.id = r.artist_id
order by
    r.score desc;

Answer 2

I suggest you let PostGreSQL return the set in any arbitrary order (especially since it's difficult to do fine-grained SQL-level control from a Django interface), then sort it in the way you wish in Python -- theresultset.sort(key=yourlistofids.index) should do fine (when theresultset is the arbitrary-order list resulting from the database and yourlistofids is the list whose order you want to preserve).