10

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.

Uasthana
  • 1,645
  • 5
  • 16
  • 24

4 Answers4

9

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

Or simply

select first_column, min(second_column)
from the_table
group by first_column;
Zegarek
  • 6,424
  • 1
  • 13
  • 24
  • 1
    @Uasthana: hmm, you said "*to remove duplicates from a table*". –  Oct 08 '16 at 22:42
  • 1
    This will delete rows 1 and 2 and 4... I think he just wants to delete row 4. – Alien Life Form Dec 21 '16 at 20:40
  • 1
    Isn't it ``having count(*) >= 1`` ? As it's now, it will also delete non-duplicated records too (with only one instance). – Mariano Anaya Nov 10 '17 at 11:10
  • Yes, it should be `having count(*) >= 1` @MarianoAnaya. Better is to just remove the `having` altogether. I nearly deleted rows that I needed with it. Please remove the `having` clause, @a_horse. – poshest Apr 19 '21 at 09:07
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.

Hack-R
  • 22,422
  • 14
  • 75
  • 131
1
  • 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
    );
wildplasser
  • 43,142
  • 8
  • 66
  • 109
  • Just out of curiosity, if there is a need to preserve rows based on more conditions, and not just delete random (which I believe `ctid` is doing) then approach with `ctid` shouldn't be used, right? I mean that it's not stable on the long run. – Kamil Gosciminski Oct 08 '16 at 13:06
  • 1
    ctid is used as a last resort, if no other colums are available to discriminate between the various candidates for deletion or selection. (other DBMS's have similar pseudo columns, with different names) In this particular case `second_col` could have been used, keeping only the lowest (or highest). – wildplasser Oct 08 '16 at 13:11
  • There is nothing wrong with my answner(s). there *might be* something wong with (the way you pose) your question, though. – wildplasser Oct 08 '16 at 23:40
0

So basically I did this 

 create temp t1 as 
 select first, min (second) as second
 from df1 
 group by first

 select * from df1 
 inner join t1 on t1.first = df1.first and t1.second = df1.second

Its a satisfactory answer. Thanks for your help @Hack-R

Uasthana
  • 1,645
  • 5
  • 16
  • 24