Marking duplicate records 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 &lt; a.id 
  order by 
    id asc 
  limit 1
  );


Is there a more efficient way to accomplish this?
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.