I have a table in my Postgres database where I forgot to insert a unique index. because of that index that i have now duplicated values. How to remove the duplicated values? I want to add a unique index on the fields translationset_Id and key.
-
2Describe which values you want to remove.jarlh– jarlh2016-11-21 10:02:32 +00:00Commented Nov 21, 2016 at 10:02
-
For safe delete refer thisViki888– Viki8882016-11-21 10:06:03 +00:00Commented Nov 21, 2016 at 10:06
-
I want to remove the 2nd record, 4th record, 7th record, 11th record and 13th record. (see image)C.B.– C.B.2016-11-21 10:06:31 +00:00Commented Nov 21, 2016 at 10:06
Add a comment
|
4 Answers
I think you are asking for 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);
2 Comments
Tim Biegeleisen
This may not be completely correct. It appears the partition should only be on the
translationset_id and key columns, not others.
Kostasfra
well this is like a blueprint that you can follow to solve your problem.I mostly use this for remembering the procedure.
I think the most efficient way to do this is below.
DELETE FROM
table_name a
USING table_name b
WHERE
a.id < b.id and
a.same_column = b.same_column;
Comments
It appears that you only want to delete records which are duplicate with regard to the translationset_id column. In this case, we can use Postgres' row number functionality to discern between duplicate rows, and then to delete those duplicates.
WITH cte AS
(
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY translationset_id, key) AS rnum
FROM yourTable t
)
DELETE FROM yourTable
WHERE translationset_id IN (SELECT translationset_id FROM cte WHERE rnum > 1)
1 Comment
C.B.
Where is the key column? "I have updated the prtScr"
delete from mytable
where exists (select 1
from mytable t2
where t2.name = mytable.name and
t2.address = mytable.address and
t2.zip = mytable.zip and
t2.ctid > mytable.ctid
);
