PostgreSQL Removing duplicates

Question

I am working on postgres query to remove duplicates from a table. The following table is dynamically generated and I want to write a select query which will remove the record if the first row has duplicate values.

The table looks something like this

Ist col  2nd col
 4        62
 6        34
 5        26
 5        12

I want to write a select query which remove either row 3 or 4.

Answer 1

There is no need for an intermediate table:

delete from df1
where ctid not in (select min(ctid)
                   from df1
                   group by first_column);

If you are deleting many rows from a large table, the approach with an intermediate table is probably faster.

---

If you just want to get unique values for one column, you can use:

select distinct on (first_column) *
from the_table
order by the_table;

Or simply

select first_column, min(second_column)
from the_table
group by first_column;

Answer 2

             select count(first) as cnt, first, second 
             from df1 
             group by first
             having(count(first) = 1)

if you want to keep one of the rows (sorry, I initially missed it if you wanted that):

             select first, min(second) 
             from df1 
             group by first

Where the table's name is df1 and the columns are named first and second.

You can actually leave off the count(first) as cnt if you want.

At the risk of stating the obvious, once you know how to select the data you want (or don't want) the delete the records any of a dozen ways is simple.

If you want to replace the table or make a new table you can just use create table as for the deletion:

             create table tmp as 
             select count(first) as cnt, first, second 
             from df1 
             group by first
             having(count(first) = 1);

             drop table df1;

             create table df1 as select * from tmp;

or using DELETE FROM:

DELETE FROM df1 WHERE first NOT IN (SELECT first FROM tmp);

You could also use select into, etc, etc.

Answer 3

• if you want to SELECT unique rows:

---

SELECT * FROM ztable u
WHERE NOT EXISTS (      -- There is no other record
    SELECT * FROM ztable x
    WHERE x.id = u.id   -- with the same id
    AND x.ctid < u.ctid -- , but with a different(lower) "internal" rowid
    );                  -- so u.* must be unique

---

• if you want to SELECT the other rows, which were suppressed in the previous query:

---

SELECT * FROM ztable nu
WHERE EXISTS (           -- another record exists
    SELECT * FROM ztable x
    WHERE x.id = nu.id   -- with the same id
    AND x.ctid < nu.ctid -- , but with a different(lower) "internal" rowid
    );

---

• if you want to DELETE records, making the table unique (but keeping one record per id):

---

DELETE FROM ztable d
WHERE EXISTS (          -- another record exists
    SELECT * FROM ztable x
    WHERE x.id = d.id   -- with the same id
    AND x.ctid < d.ctid -- , but with a different(lower) "internal" rowid
    );