如何在DELETE语句中使用行的物理位置（ROWID） [英] How to use the physical location of rows (ROWID) in a DELETE statement

问题描述

我有一个表有很多重复的行，没有主键。

我想删除只是重复的记录，但是当我尝试这样做会删除所有对等体。 p>

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

解决方案

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

所以如果你想查看它使用QUERY像这样：

  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 b $ b 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）; 
  

I have a table that has a lot of duplicated rows and no primary key.
I want to remove just the duplicated records, but when I try to do this it would remove all peers.

How can I find the ROWID from a table in Postgres?

解决方案

On PostgreSQL the physical location of the row is called CTID.

So if you want to view it use a QUERY like this:

SELECT CTID FROM table_name

To use it on a DELETE statement to remove the duplicated records use it like this:

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);

Remember that table_name is the desired table and other_columns are the columns that you want to use to filter that.

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);

这篇关于如何在DELETE语句中使用行的物理位置（ROWID）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！