3

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?

ateymour
  • 199
  • 3
  • 11
  • Would something like `SELECT DISTINCT ON (upper(my_column)) my_column` work? (Edited to fix syntax) – Jiří Baum Aug 18 '21 at 02:21
  • BTW, have you looked at [the accepted answer on the question you linked](https://stackoverflow.com/a/1152371/683329)? It's literally this problem, for this database, returning exactly what you're asking for? You may need to clarify your question if this is not what you're after... – Jiří Baum Aug 18 '21 at 02:34
  • You're right. This does the trick. I misread the answer there. I thought it was just `DISTINCT` not `DISTINCT ON` – ateymour Aug 18 '21 at 16:13
  • No worries; easy distinction to miss :-) – Jiří Baum Aug 18 '21 at 23:47

2 Answers2

7

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

Jiří Baum
  • 6,697
  • 2
  • 17
  • 17
3

You can use an aggregation function:

SELECT MAX(my_column)
FROM my_table
GROUP BY upper(my_column);

This returns one value. If you want all the values:

SELECT ARRAY_AGG(DISTINCT my_column)
FROM my_table
GROUP BY upper(my_column);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786