Take the 2-minute tour ×

I have a table that has a schema like this:

create_table "questions_tags", :id => false, :force => true do |t|
        t.integer "question_id"
        t.integer "tag_id"

      add_index "questions_tags", ["question_id"], :name => "index_questions_tags_on_question_id"
      add_index "questions_tags", ["tag_id"], :name => "index_questions_tags_on_tag_id"

I would like to remove records that are duplicates, i.e. they have both the same tag_id and question_id as another record.

What does the SQL look like for that?

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

In my experience (and as shown in many tests) NOT IN like demonstrated by @gsiems is rather slow, particularly with long lists.
This query (doing the same, exactly) should be much faster:

DELETE FROM questions_tags q
   FROM   questions_tags q1
   WHERE  q1.ctid < q.ctid
   AND    q.question_id = q1.question_id
   AND    q.tag_id = q1.tag_id

With big tables this will be faster by several orders of magnitude.


I ran a test case with this table matched to your question and 100k rows:

CREATE TABLE questions_tags(
  question_id integer NOT NULL
 ,tag_id      integer NOT NULL

INSERT INTO questions_tags (question_id, tag_id)
SELECT (random()* 100)::int, (random()* 100)::int
FROM   generate_series(1, 100000);

ANALYZE questions_tags;

Indexes do not help in this case.



The SQLfiddle times out.
Tried the same locally but I canceled it, too, after several minutes.

Finishes in half a second in this SQLfiddle.

Total runtime: 556.197 ms


If you are going to delete most of the rows, it will be faster to select the survivors into another table, drop the original and rename the survivor's table. Careful, this has implications if you have view or foreign keys (or other dependencies) defined on the original.

If you have dependencies and want to keep them, you could:

  • Drop all foreign keys and indexes - for performance.
  • SELECT survivors to a temporary table.
  • TRUNCATE the original.
  • Re-INSERT survivors.
  • Re-CREATE indexes and foreign keys. Views can just stay, they have no impact on performance. More here or here.
share|improve this answer
++ for the exists solution. Much better than my suggestion. –  gsiems Mar 14 '13 at 1:53
add comment

You can use the ctid to accomplish that. For example:

Create a table with duplicates:

=# create table foo (id1 integer, id2 integer);

=# insert into foo values (1,1), (1, 2), (1, 2), (1, 3);

=# select * from foo;
 id1 | id2 
   1 |   1
   1 |   2
   1 |   2
   1 |   3
(4 rows)

Select the duplicate data:

=# select foo.ctid, foo.id1, foo.id2, foo2.min_ctid
-#  from foo
-#  join (
-#      select id1, id2, min(ctid) as min_ctid 
-#          from foo 
-#          group by id1, id2 
-#          having count (*) > 1
-#      ) foo2 
-#      on foo.id1 = foo2.id1 and foo.id2 = foo2.id2
-#  where foo.ctid <> foo2.min_ctid ;
 ctid  | id1 | id2 | min_ctid 
 (0,3) |   1 |   2 | (0,2)
(1 row)

Delete the duplicate data:

=# delete from foo
-# where ctid not in (select min (ctid) as min_ctid from foo group by id1, id2);

=# select * from foo;
 id1 | id2 
   1 |   1
   1 |   2
   1 |   3
(3 rows)

In your case the following should work:

delete from questions_tags
    where ctid not in (
        select min (ctid) as min_ctid 
            from questions_tags 
            group by question_id, tag_id
share|improve this answer
Where can I read more about this ctid? Thanks. –  marcamillion Mar 13 '13 at 9:16
@marcamillion -- The documentation has a short blurb on ctids at postgresql.org/docs/current/static/ddl-system-columns.html –  gsiems Mar 13 '13 at 15:25
What does ctid stand for? –  marcamillion Mar 14 '13 at 5:40
@marcamillion -- tid == "tuple id", not sure what the c means. –  gsiems Mar 14 '13 at 14:04
add comment

Your Answer


