-4

I have a table with three columns. I want to create a unique index about all three columns with a counter, which tells me, where the duplicates are. I also want to delete them.           

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
xandl1994
  • 41
  • 2
  • 2
  • 12
  • CREATE TABLE products ( product_no integer, name_no integer, price integer ); INSERT INTO products (product_no, name_no, price) VALUES (1, 3, 99), (2, 5, 199), (3, 6, 299), (3, 3, 1); select * from products; CREATE UNIQUE INDEX index_name on products(product_no, name_no, price); and I get no error but I have duplicates or? – xandl1994 Sep 11 '14 at 10:21
  • Well: if you have a unique index, you cannot have duplicates. And if you have duplicates, you cannot have a unique index. – joop Sep 11 '14 at 10:21
  • I want to delete all duplicate values And where they are in the table – xandl1994 Sep 11 '14 at 10:24
  • There are two records with product_no = 3. And there are two records with name_no=3. Which one would you call a duplicate ? – joop Sep 11 '14 at 10:34
  • http://stackoverflow.com/questions/3777633/delete-duplicate-rows-dont-delete-all-duplicate –  Sep 11 '14 at 10:37
  • http://stackoverflow.com/questions/5921167/delete-duplicate-rows-from-table –  Sep 11 '14 at 10:37
  • http://stackoverflow.com/questions/24669366/find-duplicate-rows-and-keep-the-latest-one-delete-the-rest –  Sep 11 '14 at 10:37
  • http://stackoverflow.com/questions/243567/remove-duplicate-from-a-table –  Sep 11 '14 at 10:38

1 Answers1

1

I'm not sure what your problem really is, but if you need to delete duplicates in your postgre database try this:

DELETE FROM tablename 
    WHERE id IN (SELECT id
          FROM (SELECT id,
                         row_number() over (partition BY column1, column2, column3 ORDER BY id) AS rnum
                 FROM tablename) t
          WHERE t.rnum > 1);

"Sometimes a timestamp field is used instead of an ID field."

It helped me to delete duplicates from my tables.

You can find this solutiona at: http://wiki.postgresql.org/wiki/Deleting_duplicates

Vojta
  • 810
  • 10
  • 16