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
SELECT name FROM friends ORDER BY name
[name] Paul Tony adam
To get them alphabetically ordered regardless of the case, I can use the
SELECT name FROM friends ORDER BY lower(name)
[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?