3

I have a list of things, some of which are duplicates.

create table things (
  id int primary key,
  title text not null,
  author text not null,
  duplicate_of int null references things(id)
);

I'd like to mark duplicates as such (can't delete them). The first item (defined by the lowest id) should never be marked as a duplicate. Its duplicates should have their duplicate_of value set to its id.

This is what I have so far:

update things a set duplicate_of = (
  select 
    id 
  from things b 
  where 
    b.title = a.title 
    and b.author = a.author 
    and b.id < a.id 
  order by 
    id asc 
  limit 1
  );

Is there a more efficient way to accomplish this?

0

2 Answers 2

3

This can be solved with the help of the FIRST_VALUE analytical function:

UPDATE
  things AS tgt
SET
  duplicate_of = src.first_id
FROM
  (
    SELECT
      id
    , title
    , author
    , FIRST_VALUE(id) OVER (PARTITION BY title, author ORDER BY id ASC) as first_id
    FROM
      things
  ) AS src
WHERE
  tgt.id = src.id
  AND tgt.id <> src.first_id
;

The FIRST_VALUE function returns the first value in each subset defined by the PARTITION BY clause based on the sorting order defined by ORDER BY.

Suppose we have a dataset like this:

id title author duplicate_of
1 Hamlet William Shakespeare null
2 Done Quixote Miguel de Cervantes null
3 War and Peace Leo Tolstoy null
4 Hamlet William Shakespeare null
5 War and Peace Leo Tolstoy null
6 To Kill a Mockingbird Harper Lee null
7 Hamlet William Shakespeare null

For the above sample, the src derived table will evaluate like this:

id title author first_id
1 Hamlet William Shakespeare 1
2 Done Quixote Miguel de Cervantes 2
3 War and Peace Leo Tolstoy 3
4 Hamlet William Shakespeare 1
5 War and Peace Leo Tolstoy 3
6 To Kill a Mockingbird Harper Lee 6
7 Hamlet William Shakespeare 1

As you can see, for the three instances of Hamlet, the first_id calculated column shows 1, which is the ID of the first instance. Similar for the two instances of War and Peace.

Joining this set with the original table allows you to use the values of first_id to update the duplicate_of column, which is exactly what the query does. The additional filter of tgt.id <> src.first_id is there to prevent updating the first instance of each duplicate.

This is what the table ends up like after the query:

id title author duplicate_of
1 Hamlet William Shakespeare null
2 Done Quixote Miguel de Cervantes null
3 War and Peace Leo Tolstoy null
4 Hamlet William Shakespeare 1
5 War and Peace Leo Tolstoy 3
6 To Kill a Mockingbird Harper Lee null
7 Hamlet William Shakespeare 1

The test setup and the solution are available in a live demo at db<>fiddle.

More about analytical and other window functions can be read in the online PostgreSQL manual (current version).

-1

you can make use of the so called ctid (which is basically an internal thing but for a lousy hack it should do. here is an example: '

test=# CREATE TABLE x (id int);
CREATE TABLE
test=# INSERT INTO x SELECT * FROM generate_series(1, 4);
INSERT 0 4
test=# INSERT INTO x SELECT * FROM generate_series(1, 4);
INSERT 0 4
test=# SELECT ctid, * FROM x;
 ctid  | id 
-------+----
 (0,1) |  1
 (0,2) |  2
 (0,3) |  3
 (0,4) |  4
 (0,5) |  1
 (0,6) |  2
 (0,7) |  3
 (0,8) |  4
(8 rows)

test=# SELECT id, min(ctid) FROM x GROUP BY 1;
 id |  min  
----+-------
  4 | (0,4)
  1 | (0,1)
  3 | (0,3)
  2 | (0,2)
(4 rows)

test=# DELETE FROM x WHERE ctid NOT IN (SELECT min(ctid) FROM x GROUP BY id);
DELETE 4
test=# SELECT ctid, * FROM x;
 ctid  | id 
-------+----
 (0,1) |  1
 (0,2) |  2
 (0,3) |  3
 (0,4) |  4
(4 rows)
`

keep in mind; the ctid is an internal thing so be careful.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.