How to Find Duplicate Records in Postgresql

How to find duplicate records in PostgreSQL

The basic idea will be using a nested query with count aggregation:

select * from yourTable ou
where (select count(*) from yourTable inr
where inr.sid = ou.sid) > 1

You can adjust the where clause in the inner query to narrow the search.

---

There is another good solution for that mentioned in the comments, (but not everyone reads them):

select Column1, Column2, count(*)
from yourTable
group by Column1, Column2
HAVING count(*) > 1

Or shorter:

SELECT (yourTable.*)::text, count(*)
FROM yourTable
GROUP BY yourTable.*
HAVING count(*) > 1

How to find duplicate records and update using postgresql?

You can use RANK () OVER function like below

which will give you each entry ranked based on last modified date for each userid group.

Then you can write update query to update isactive to false where device_rank ! =1

select id,userid,deviceid,isactive,last_modified,
RANK () OVER ( 
        PARTITION BY userid
        ORDER BY last_modified DESC
    ) device_rank 
from deviceTable

Find duplicate rows with PostgreSQL

Here is my take on it.

select * from (
  SELECT id,
  ROW_NUMBER() OVER(PARTITION BY merchant_Id, url ORDER BY id asc) AS Row
  FROM Photos
) dups
where 
dups.Row > 1

Feel free to play with the order by to tailor the records you want to delete to your specification.

SQL Fiddle => http://sqlfiddle.com/#!15/d6941/1/0

---

SQL Fiddle for Postgres 9.2 is no longer supported; updating SQL Fiddle to postgres 9.3

duplicate records from postgresql sql query

If you don't want duplicates, the simplest solution is to use select distinct:

SELECT DISTINCT p.id, pp.parameter_id
FROM products a JOIN
     products_parameters pp
     ON pp.product_id = p.id 
WHERE p.category_id = 14 AND pp.parameter_id = 22
ORDER BY p.id;

Your question doesn't have enough information to specify why you are getting duplicates, but presumably because you are only choosing one column from each of the tables and other columns are different.

Note the other changes to the query:

• The table aliases are meaningful rather than arbitrary letter.
• The WHERE clause turns the LEFT JOIN into an inner join anyway, so this version properly expresses the JOIN.

postgresql find duplicates in column with ID

Use Count()Over() window aggregate function

Select * from
(
select Id, Firstname, count(1)over(partition by Firstname) as Cnt
from yourtable
)a
Where Cnt > 1

Find duplicate rows in PostgreSQL with additional criteria

You want a condition which is checked after grouping, not before, i.e. HAVING instead of WHERE. Note that the condition should either be one of grouping fields or aggregate (just like in SELECT). You should be able to count number of rows which satisfies the condition like in this answer:

SELECT case_id, number, count(*)
FROM entries
GROUP BY case_id, number
HAVING (count(*) > 1) AND (count(CASE WHEN filed_on IS NULL THEN 1 END) >= 1)

See SQL Fiddle

select all duplicates from a table in postgres

Something like this might be what you're looking for.

  SELECT columns_that_define_duplicates -- SELECT item_id, name, slug perhaps?
       , count(*)
    FROM eve_online_market_groups
GROUP BY columns_that_define_duplicates
  HAVING count(*) > 1;

Find duplicate entries in a column in PostgreSQL graphql hasura?

You can simply create a view and query it like a normal table;

let us assume there's a people table with a first_name column. To find duplicates

create or replace view duplicate_first_names as select p.first_name,
count(p.first_name) from people p group by(p.first_name) having
count(p.first_name) > 1

or if you want to return the entire row of the duplicate column, you can add a sub-query:

create or replace view duplicate_people_first_names as select * from
people p where (select count(*) from people ppl where ppl.first_name =
p.first_name) > 1;

Find SQL duplicate with specific condition

Another way :

select tn.id,tn.address_id,tn.state
from tableName tn
inner join (select max(id) as id ,count(address_id) as nr_count
            from tableName
            where state='A'
            group by address_id
            ) as t1 on tn.id=t1.id
where   t1.nr_count >1;   

Demo

You could use window function:

select max(id) as id ,address_id,state
from (
   SELECT id, address_id,state
        , count(*) OVER ( PARTITION BY address_id  ) AS cnt
   FROM   tableName
   where state='A'
   ) as t1
where cnt>1
group by address_id,state;    

Demo

---

Related Topics