I have a string as input, that represents an hostname, and a table that contains suffixes (e.g.
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
uk), it may very well return
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?