如何在DELETE语句中使用行的物理位置(ROWID)

Question

我有一个表，它有很多重复的行，没有主键
我只想删除重复的记录，但当我尝试这样做时，它会删除所有对等记录。

如何从Postgres中的表中找到ROWID？

Answer 1

在PostgreSQL上，行的物理位置称为CTID。

因此，如果你想查看它，请使用这样的查询：

SELECT CTID FROM table_name

要在DELETE语句中使用它来删除重复的记录，请这样使用它：

DELETE FROM table_name WHERE CTID NOT IN (
  SELECT RECID FROM 
    (SELECT MIN(CTID) AS RECID, other_columns 
      FROM table_name GROUP BY other_columns) 
  a);

请记住，table_name是所需的表，other_columns是要用于筛选的列。

Ie:

DELETE FROM user_department WHERE CTID NOT IN (
  SELECT RECID FROM 
    (SELECT MIN(CTID) AS RECID, ud.user_id, ud.department_id
      FROM user_department ud GROUP BY ud.user_id, ud.department_id) 
  a);

Answer 2

通过一个查询级别简化这一点：

DELETE FROM table_name
WHERE  ctid NOT IN (
   SELECT min(ctid)
   FROM   table_name
   GROUP  BY $other_columns);

其中重复由CCD_ 2中的等式来定义
SELECT列表中不需要包含GROUP BY子句中的列，因此不需要其他子查询。

当前手册中的ctid。

Answer 3

如果要根据唯一的id列(或时间戳(进行删除，则应考虑使用row_number()，因为当您只想保留最近的记录等时，单独使用ctid并不总是可靠的。

WITH d 
     AS (SELECT ctid c, 
                row_number() 
                  OVER ( 
                    partition BY s 
                    ORDER BY id) rn 
         FROM   t) 
DELETE FROM t 
WHERE  ctid IN (SELECT c 
               FROM   d 
               WHERE  rn > 1)  ; 

演示