6

I need to find duplicates in a table. In MySQL I simply write:

SELECT *,count(id) count FROM `MY_TABLE`
GROUP BY SOME_COLUMN ORDER BY count DESC

This query nicely:

  • Finds duplicates based on SOME_COLUMN, giving its repetition count.
  • Sorts in desc order of repetition, which is useful to quickly scan major dups.
  • Chooses a random value for all remaining columns, giving me an idea of values in those columns.

Similar query in Postgres greets me with an error:

column "MY_TABLE.SOME_COLUMN" must appear in the GROUP BY clause or be used in an aggregate function

What is the Postgres equivalent of this query?

PS: I know that MySQL behaviour deviates from SQL standards.

jerrymouse
  • 16,964
  • 16
  • 76
  • 97

4 Answers4

12

Back-ticks are a non-standard MySQL thing. Use the canonical double quotes to quote identifiers (possible in MySQL, too). That is, if your table in fact is named "MY_TABLE" (all upper case). If you (more wisely) named it my_table (all lower case), then you can remove the double quotes or use lower case.

Also, I use ct instead of count as alias, because it is bad practice to use function names as identifiers.

Simple case

This would work with PostgreSQL 9.1:

SELECT *, count(id) ct
FROM   my_table
GROUP  BY primary_key_column(s)
ORDER  BY ct DESC;

It requires primary key column(s) in the GROUP BY clause. The results are identical to a MySQL query, but ct would always be 1 (or 0 if id IS NULL) - useless to find duplicates.

Group by other than primary key columns

If you want to group by other column(s), things get more complicated. This query mimics the behavior of your MySQL query - and you can use *.

SELECT DISTINCT ON (1, some_column)
       count(*) OVER (PARTITION BY some_column) AS ct
      ,*
FROM   my_table
ORDER  BY 1 DESC, some_column, id, col1;

This works because DISTINCT ON (PostgreSQL specific), like DISTINCT (SQL-Standard), are applied after the window function count(*) OVER (...). Window functions (with the OVER clause) require PostgreSQL 8.4 or later and are not available in MySQL.

Works with any table, regardless of primary or unique constraints.

The 1 in DISTINCT ON and ORDER BY is just shorthand to refer to the ordinal number of the item in the SELECT list.

SQL Fiddle to demonstrate both side by side.

More details in this closely related answer:

count(*) vs. count(id)

If you are looking for duplicates, you are better off with count(*) than with count(id). There is a subtle difference if id can be NULL, because NULL values are not counted - while count(*) counts all rows. If id is defined NOT NULL, results are the same, but count(*) is generally more appropriate (and slightly faster, too).

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks @Erwin. I am using 9.0.4 and it says "No function matches the given name and argument types". Will try again after upgrading it to 9.1. – jerrymouse May 01 '12 at 13:48
  • @jerrymouse: Upgrading to 9.1 is a good idea for a number of reasons. Added info for pre-9.1 and generally edited my answer. First draft was not precise. – Erwin Brandstetter May 01 '12 at 14:53
  • @aleroot: It's the combination of window functions and `DISTINCT`. The important thing is that `DISTINCT` is applied *after* window functions (which are applied after aggregate functions). – Erwin Brandstetter May 01 '12 at 15:49
3

Here's another approach, uses DISTINCT ON:

select 

  distinct on(ct, some_column) 

  *,
  count(id) over(PARTITION BY some_column) as ct

from my_table x
order by ct desc, some_column, id

Data source:

CREATE TABLE my_table (some_column int, id int, col1 int);

INSERT INTO my_table  VALUES
 (1, 3,  4)
,(2, 4,  1)
,(2, 5,  1)
,(3, 6,  4)
,(3, 7,  3)
,(4, 8,  3)
,(4, 9,  4)
,(5, 10, 1)
,(5, 11, 2)
,(5, 11, 3);

Output:

SOME_COLUMN ID          COL1        CT
5           10          1           3
2           4           1           2
3           6           4           2
4           8           3           2
1           3           4           1

Live test: http://www.sqlfiddle.com/#!1/e2509/1

DISTINCT ON documentation: http://www.postgresonline.com/journal/archives/4-Using-Distinct-ON-to-return-newest-order-for-each-customer.html

Michael Buen
  • 38,643
  • 9
  • 94
  • 118
1

mysql allows group by to omit non-aggregated selected columns from the group by list, which it executes by returning the first row found for each unique combination of grouped by columns. This is non-standard SQL behaviour.

postgres on the other hand is SQL standard compliant.

There is no equivalent query in postgres.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • My (edited) query should be a PostgreSQL equivalent to the MySQL query - if I did not miss something. – Erwin Brandstetter May 01 '12 at 15:08
  • 1
    Postgres has, and it is saner than MySql had offered. Look at the functional dependency on GROUP BY http://www.ienablemuch.com/2010/08/postgresql-recognizing-functional.html – Michael Buen May 02 '12 at 03:44
  • If you really want to simulate in Postgresql the MySQL's GROUP BY with randomly selected non-aggregated columns, use DISTINCT ON. But I never use DISTINCT ON for that, there are other legitimate use for DISTINCT ON, case in point: http://stackoverflow.com/questions/1104977/how-to-get-the-parent-given-a-child-in-sql-server-2005/1107901#1107901 And this: http://stackoverflow.com/questions/10392567/postgresql-pass-data-from-recursive-cte-onto-function/10401572#10401572 – Michael Buen May 02 '12 at 03:51
  • @MichaelBuen: I am not defending MySQL's dirty tricks, far from it. I am providing an answer to the question asked. The way I use `first_val()` in my answer is just as random as column-values that are not included in a `DISTINCT ON`. Both methods don't exactly pick random values, but implementation-specific arbitrary ones. Just like MySQL. – Erwin Brandstetter May 02 '12 at 18:06
  • @ErwinBrandstetter I'm not referring to your answer, I'm referring to the answer here :-) I just use the term random loosely here, it's just as random as it gets when a row is selected based on it being a first row in alphabetical sorting or physical location in database rows. Teehee, I might name my child Aaron because of this; however, the query might be sorted on Lastname, but I can't change our family name, so this is what I meant by random. Postgres is not spared of this criticism on DISTINCT ON too, but there are far more atrocities committed with MySql's GROUP BY – Michael Buen May 03 '12 at 00:08
1

Here is a self-joined CTE, which allows you to use select *. key0 is the intended unique key, {key1,key2} are the additional key elements needed to address the currently non-unique rows. Use at your own risk, YMMV.

WITH zcte AS (
        SELECT DISTINCT tt.key0
        , MIN(tt.key1) AS key1
        , MIN(tt.key2) AS key2
        , COUNT(*) AS cnt
        FROM ztable tt
        GROUP BY tt.key0
        HAVING COUNT(*) > 1
        )
SELECT zt.*
        , zc.cnt AS cnt
FROM ztable zt
JOIN zcte zc ON zc.key0 = zt.key0 AND zc.key1 =  zt.key1 AND zc.key2 = zt.key2
ORDER BY zt.key0, zt.key1,zt.key2
      ;

BTW: to get the intended behaviour for the OP, the HAVING COUNT(*) > 1 clause should be omitted.

wildplasser
  • 43,142
  • 8
  • 66
  • 109