I want to remove duplicate rows return from a SELECT Query in Postgres
I have the following query
SELECT DISTINCT name FROM names ORDER BY name
But this somehow does not eliminate duplicate rows?
-
I'm using an ancient PostgreSQL 7.3.4 versionElitmiar– Elitmiar2009-07-20 08:30:14 +00:00Commented Jul 20, 2009 at 8:30
-
possible duplicate of Remove duplicate from a tableJonathan Hall– Jonathan Hall2014-03-04 20:32:29 +00:00Commented Mar 4, 2014 at 20:32
-
It's not a duplicate. @Flimzy ... a different question, with a fairly vague title.ocodo– ocodo2015-03-29 11:17:38 +00:00Commented Mar 29, 2015 at 11:17
Add a comment
|
4 Answers
PostgreSQL is case sensitive, this might be a problem here DISTINCT ON can be used for case-insensitive search (tested on 7.4)
SELECT DISTINCT ON (upper(name)) name FROM names ORDER BY upper(name);
Comments
Maybe something with same-looking-but-different characters (like LATIN 'a'/CYRILLIC 'а')
Comments
Don't forget to add a trim() on that too. Or else 'Record' and 'Record ' will be treated as separate entities. That ended up hurting me at first, I had to update my query to:
SELECT DISTINCT ON (upper(trim(name))) name FROM names ORDER BY upper(trim(name));
Comments
In Postgres 9.2 and greater you can now cast the column to a CITEXT type or even make the column that so you don't have to cast on select.
SELECT DISTINCT name::citext FROM names ORDER BY name::citext
