Select Distinct (case insensitive) on Postgres

Question

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?

Would something like SELECT DISTINCT ON (upper(my_column)) my_column work? (Edited to fix syntax) — Jiří Baum
– Jiří Baum, Commented Aug 18, 2021 at 2:21
BTW, have you looked at the accepted answer on the question you linked? 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
– Jiří Baum, Commented Aug 18, 2021 at 2:34
You're right. This does the trick. I misread the answer there. I thought it was just DISTINCT not DISTINCT ON — ateymour
– ateymour, Commented Aug 18, 2021 at 16:13

Jiří Baum · Accepted Answer · 2021-08-18 02:28:26Z

8

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

answered Aug 18, 2021 at 2:28

Jiří Baum

6,9882 gold badges19 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2021-08-18 02:29:04Z

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);

answered Aug 18, 2021 at 2:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Select Distinct (case insensitive) on Postgres

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related