Eliminating Duplicate rows in Postgres

Question

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?

possible duplicate of [Remove duplicate from a table](http://stackoverflow.com/questions/243567/remove-duplicate-from-a-table) — Jonathan Hall, Mar 04 '14 at 20:32
It's not a duplicate. @Flimzy ... a different question, with a fairly vague title. — ocodo, Mar 29 '15 at 11:17

score 10 · Accepted Answer · answered Jul 20 '09 at 08:37

10

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

answered Jul 20 '09 at 08:37

ymv

2,123
13
21

score 1 · Answer 2 · answered Jul 20 '09 at 08:46

1

Maybe something with same-looking-but-different characters (like LATIN 'a'/CYRILLIC 'а')

answered Jul 20 '09 at 08:46

ymv

2,123
13
21

score 1 · Answer 3 · answered May 06 '16 at 15:37

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

score 0 · Answer 4 · answered Dec 27 '16 at 21:47

0

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

answered Dec 27 '16 at 21:47

JT Turner

502
2
14

Eliminating Duplicate rows in Postgres

4 Answers4

Linked