sql - 在没有主键的情况下更新/删除列中的任意行

Question

我正在构建一个工具，该工具将显示给定 PostgreSQL 数据库(客户的遗留应用程序)中的所有表，然后用户将深入挖掘并可以看到给定表中的所有数据。它本质上是一个数据库查看器。

下一步将允许用户更新每一行，其方式与更新 Airtable 中的数据的方式类似。

虽然对于大多数列，我将拥有主键，因此我可以使用它来构建适当的 Update ... where ID=? 语句，但我意识到情况可能并非总是如此。例如，对于某些连接表，我没有 ID 或任何其他主键。

我仍然希望拥有这样的功能，即用户可以查看从这些列中显示的数据网格，通过单击鼠标选择一行并提供新值。

PostgreSQL 曾经在这种情况下使用 OID 来唯一标识行，但即使对于我正在处理的遗留数据库，情况也不再如此。

我能想到的唯一解决方案是使用偏移量/排序顺序来确定要更新哪一行，但是如果在此期间排序发生变化或用户删除/添加一些行，这会导致竞争条件。

关于如何更新此类“匿名”行的任何想法？

Answer 1

Postgres 中的每个表都有一个系统列ctid，它明确标识了一行。示例:

drop table if exists my_table;
create table my_table(id int, str text);
insert into my_table values
(1, 'one'),
(1, 'two'),
(2, 'one');

select ctid, *
from my_table;

 ctid  | id | str 
-------+----+-----
 (0,1) |  1 | one
 (0,2) |  1 | two
 (0,3) |  2 | one
(3 rows)

您可以使用删除或更新中的列:

delete from my_table
where ctid = '(0,2)'
returning *

 id | str 
----+-----
  1 | two
(1 row)

DELETE 1

但是请注意，根据 the documentation:，不能保证一行始终具有相同的 ctid

ctid

The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change if it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. The OID, or even better a user-defined serial number, should be used to identify logical rows.