oracle rowid and postgresql ctid(二)
values (2,'orange');
INSERT 0 1
testuser=# insert into t2 values (2,'orange');
INSERT 0 1
testuser=# insert into t2 values (3,'banana');
INSERT 0 1
testuser=# insert into t2 values (3,'banana');
INSERT 0 1
testuser=# select * from t2;
id | name
----+--------
1 | apple
1 | apple
1 | apple
2 | orange
2 | orange
2 | orange
2 | orange
3 | banana
3 | banana
查询重复的数据：
testuser=# select ctid,* from t2 where ctid in (select min(ctid) from t2 group by id);
ctid | id | name
-------+----+--------
(0,1) | 1 | apple
(0,4) | 2 | orange
(0,8) | 3 | banana
删除重复数据并查看结果：
testuser=# delete from t2 where ctid not in (select min(ctid) from t2 group by id);
DELETE 6
testuser=# select * from t2;
id | name
----+--------
1 | apple
2 | orange
3 | banana
(3 rows)