Select Distinct (case insensitive) on Postgres

This has been asked here and a few other places before but seems like the suggested answers either don’t apply to postgres or don’t work in this situation.

I’m looking to select distinct column names, eg:

SELECT DISTINCT column_name FROM table_name WHERE ... ORDER BY column_name however I’m looking to eliminate case sensitive duplicates (eg A and a should be considered the same thing)

I tried COLLATE but all available formats were case sensitive. And changing case via LOWER() OR UPPER() wont work because In this situation I need the case information.

I thought about something like this to grab unique values but still maintain the case:

SELECT DISTINCT upper(my_column) as upper_case, my_column
FROM my_table
ORDER BY upper(my_column)

But introducing my_column in the distinct query negates the whole thing.

How can I get unique values (case insensitive) without modifying the case of the results itself?

Answer

In PostgreSQL (but not many other databases), you can use a DISTINCT ON clause:

SELECT DISTINCT ON (upper(my_column)) my_column
FROM my_table
ORDER BY upper(my_column)

You can even choose which of the results you get, by adding another column to the ORDER BY clause to make the desired result appear first:

SELECT DISTINCT ON (upper(my_column)) my_column
FROM my_table
ORDER BY upper(my_column), other_column

Documentation: DISTINCT Clause