Case-insensitive order while preserving the case of the returned strings

Question

I have noticed that when ordering, PostgreSQL (I'm currently working with 11 but I think the same applies to 10 and 9) is case sensitive. It returns upper-cased string before lower-cased one. For instance, let's say I have the friends table with 3 records and the first_name column contains respectively adam, Tony, Paul.

SELECT name
FROM friends
ORDER BY name

returns

[name]
 Paul
 Tony
 adam

To get them alphabetically ordered regardless of the case, I can use the lower function.

SELECT name
FROM friends
ORDER BY lower(name)

returns

[name]
 adam
 Paul
 Tony

Now I need to join the friends table with the cities table in order to get only the friends associated with a city while keeping the friends alphabetically ordered and without changing the case of their name .

The following query is valid

SELECT DISTINCT lower(friends.name)
FROM friends
INNER JOIN cities
ON friends.city_id = cities.id
ORDER BY lower(friends.name)

But the returned names are all lowercase. However, the query below

SELECT DISTINCT friends.name
FROM friends
INNER JOIN cities
ON friends.city_id = cities.id
ORDER BY lower(friends.name)

returns the error

ORDER BY expressions must appear in select list.

What is the simplest way to apply a case-insensitive order while preserving the case of the returned strings?

Answer 1

The solution is simple: don't use a join. This also removes the need for a DISTINCT.

select f.name
from friends f
where exists (select *
              from cities c
              where f.city_id = c.id)
order by lower(f.name);

Online example: https://rextester.com/SNA62411

Answer 2

You can specify a COLLATE clause after ORDER BY however it seems there is no clear collation modifier for beeing case sensitive or not — it depends on the OS and encoding.

I guess you can sort by lower/upper(col), but you might need to watch out for performance problems if you don’t have a function index on that expression. (You would need to add upper(friends.name), friends.name to your select list and ignore the sort column)

There are quite some topics dealing with that on SE, for example: Why is my PostgreSQL ORDER BY case-insensitive?