Sort results of a query by the order of OR clause I have a string as input, that represents an hostname, and a table that contains suffixes (e.g. co.uk, uk, etc).

I'd like the database to return the records based on the order of the where clause. For example, let's assume the suffixes table contains the following entries:

| id | name   |
|----|--------|
| 1  | co.uk  |
| 2  | uk     |
| 3  | foo.uk |


Assuming the input is example.co.uk, I will produce a query like the following one:

SELECT *
FROM suffixes
WHERE
  name = "example.co.uk" OR
  name = "co.uk" OR
  name = "uk"
LIMIT 1


The result should be:

| 1  | co.uk  |


However, from tests I executed, it is not guaranteed that PG follows the order of the OR conditions by defaults. In some cases, when there are multiple entries matching the OR conditions (e.g. in this case co.uk and uk), it may very well return uk.

In fact, if I take the same query and swap the OR conditions:

SELECT *
FROM suffixes
WHERE
  name = "uk" OR
  name = "co.uk"
LIMIT 1


in most cases it returned the same result of the previous query.

How can I tell PostgreSQL to return the first match of the OR query, in the order specified in the query?
There are two ways to do this in Postgres:

Join your table against a virtual table build from a values list:

with names (idx, name) as (
  values 
    (1, 'example.co.uk'), 
    (2, 'co.uk'), 
    (3, 'uk')
)
select s.*
from suffixes s
  join names n on n.name = s.name
order by n.idx
limit 1;


Another shorter version would be to join against an array:

select s.*
from suffixes s
  join unnest(array['example.co.uk', 'uk', 'co.uk']) with ordinality as n(name, idx) 
    on n.name = s.name
order by n.idx
limit 1


The second version has the advantage, that you don't need to supply the additional index because that's implicitly defined through the order of the values inside the array.