Deleting identical rows

Every table has a ctid column - always unique
Tables *may* have an oid column - usually unique
Both must be specifically called: SELECT oid, ctid, * FROM ...
If all other values in a row are the same, the oid or ctid can help


CREATE TABLE sightings (
  who varchar,
  sdate date
);

INSERT INTO sightings (who, sdate) VALUES ('Nessie',  '1982-04-14');
INSERT INTO sightings (who, sdate) VALUES ('Bigfoot', '1978-10-23');
INSERT INTO sightings (who, sdate) VALUES ('Nessie',  '1985-05-01');
INSERT INTO sightings (who, sdate) VALUES ('Elvis',   '1994-07-04');
INSERT INTO sightings (who, sdate) VALUES ('Bigfoot', '1978-10-23');
INSERT INTO sightings (who, sdate) VALUES ('Bigfoot', '1995-12-25');

One too many bigfoots. How to remove just one?
Use a transaction for safety!


SELECT * FROM sightings WHERE who='bigfoot' ORDER BY sdate;
   who   |   sdate    
---------+------------
 Bigfoot | 1978-10-23
 Bigfoot | 1978-10-23
 Bigfoot | 1995-12-25

Solution 1: Direct oid (good)


oscon=> SELECT oid, * FROM sightings WHERE who='bigfoot' ORDER BY sdate;
  oid  |   who   |   sdate    
-------+---------+------------
 17005 | Bigfoot | 1978-10-23
 17009 | Bigfoot | 1978-10-23
 17010 | Bigfoot | 1995-12-25

oscon=> BEGIN;
BEGIN

oscon=> DELETE FROM sightings WHERE oid=17005;
DELETE 1

oscon=> COMMIT;
COMMIT

Solution 2: Indirect oid (better)


oscon=> BEGIN;
BEGIN

oscon=> DELETE FROM sightings
oscon-> WHERE who='Bigfoot' AND sdate ='1978-10-23'
oscon-> AND oid NOT IN
oscon->   (SELECT oid FROM sightings WHERE who='Bigfoot' AND sdate = '1978-10-23' LIMIT 1);
DELETE 1

oscon=> COMMIT;
COMMIT

Solution 3: Indirect ctid (best - works on any table)


oscon=> SELECT ctid, * FROM sightings WHERE who='Bigfoot';
  ctid  |   who   |   sdate    
--------+---------+------------
 (0,14) | Bigfoot | 1978-10-23
 (0,18) | Bigfoot | 1978-10-23
 (0,25) | Bigfoot | 1995-12-25

oscon=> BEGIN;
BEGIN
oscon=> DELETE FROM sightings
oscon-> WHERE who='Bigfoot' AND sdate ='1978-10-23'
oscon-> AND NOT ctid =
oscon->   (SELECT ctid FROM sightings WHERE who='Bigfoot' AND sdate = '1978-10-23' LIMIT 1);
DELETE 1
oscon=> COMMIT;
COMMIT

Previous: Future of SQL [Table of Contents] Next: Counting Rows