今天遇到存储过程中使用 ORACLE 的rowid的情形：

1
2
delete from testrowid t1 where t1.rowid!=(select max(rowid) from testrowid t2
           where Trim(t1.ip)=Trim(t2.ip));
1
l_sql := 'insert into testrowid(i) select min(rowid) from testctid;
1
2
3
l_sql := 'update '||tname||' t1 set t1.name = null where
          not exists (select 1 from testctid t2 where t1.rowid = t2.i)
          ;

以下是大模型给出的结果：

在 Oracle 中, rowid 是一种伪列，可以用于唯一确定表中的一行；在 PostgreSQL 中，ctid 是一种系统列，提供了类似的功能。所以在这个转换中，rowid 被替换为 ctid 以适应 PostgreSQL 的语法。

上面的三条语句有插入、删除和更新三种不同类型的语句。

1
2
mesdb=> create table testrowid(i varchar);
CREATE TABLE
1
2
mesdb=> create table testctid(i varchar,name varchar2);
CREATE TABLE
1
2
3
4
5
6
7
8
9
10
insert into testrowid(i) select min(ctid) from testctid;
                                       ^
mesdb=> insert into testctid  values ('1','abc');
INSERT 0 1
mesdb=> insert into testctid  values ('1','abc');
INSERT 0 1
mesdb=> insert into testctid  values ('1','abc');
INSERT 0 1
mesdb=> insert into testctid  values ('1','abc');
INSERT 0 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

mesdb=> select * from testctid;
 i | name
---+------
 1 | abc
 1 | abc
 1 | abc
 1 | abc
(4 rows)

mesdb=> select min(ctid) from testctid
mesdb-> ;
  min
-------
 (0,1)
(1 row)

mesdb=>  insert into testrowid(i) select min(ctid) from testctid;
INSERT 0 1
mesdb=> select * from testrowid;
   i
-------
 (0,1)
(1 row)

mesdb=>
1
2
3
4
5
6
7
8
9
                                       ^
mesdb=> insert into testctid  values ('1','abc');
INSERT 0 1
mesdb=> insert into testctid  values ('1','abc');
INSERT 0 1
mesdb=> insert into testctid  values ('1','abc');
INSERT 0 1
mesdb=> insert into testctid  values ('1','abc');
INSERT 0 1
1
2
3
4
5
6
7
mesdb=> update  testrowid t1 set t1.name = null where
mesdb->           not exists (select 1 from testctid t2 where t1.ctid = t2.i)
mesdb->           ;
ERROR:  operator does not exist: tid = character varying
LINE 2: ... not exists (select 1 from testctid t2 where t1.ctid = t2.i)
                                                                ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

在存储过程中的报错如下：

1
operator doesnot exist: tid = character varying

也就是类型不一致：

问了AI给出的答复是：

PostgreSQL 的 ctid 类型是一个特殊的系统列，它的类型实为 tid，表示一个元组（tuple）的位置信息，而非常见的字符型或数值型。所以，当你尝试将一个 ctid 值与一个字符型或变量值进行比较时，就会出现 “operator does not exist: tid = character varying” 的错误。

你需要确保比较操作涉及的两个条件具有相同的数据类型。在你的情况下，你可能需要重新检查你的表结构和相关的 SQL 语句，确保你是在比较两个 ctid 值，而不是尝试将一个 ctid 值与其他类型的值进行比较。你可能需要使用 :: 操作符来进行必要的类型转换。

修改如下：

1
2
3
4
5
6
7
8
9
10
11
12
mesdb=> update  testctid t1 set t1.name = null where
mesdb->           not exists (select 1 from testrowid t2 where t1.ctid = t2.i::tid)
mesdb->           ;
UPDATE 3
mesdb=> select * from testctid;
 i | name
---+------
 1 | abc
 1 |
 1 |
 1 |
(4 rows)

也就是t2.i::tid 将 t2 i 列类型转换 tid 类型。

在上面的 增、删的情况下，ctid 能够很好的替换rowid，但是update可能会涉及到字段类型的转换，需要隐式的进行字段转换，其实在 insert的时候应该也进行了字段类型的转换，只不过是隐式。

其实这个改造，从第一个delete来看，他是去重的。有重复数据的保留rowid 最大的一条数据。这表中也都没有pk，理论上这应该从业务端来整改，创建pk等。

关于 PostgreSQL 中的 ctid：

在 PostgreSQL 中，CTID（Tuple ID）是一个特殊的系统列，每个表都有这一列。 CTID 代表 “元组ID”，用于标识在一个特定表中的一行。CTID 是一个包含两个 16 位整数的值，第一个是页数（从 0 开始），第二个是该页中的元组索引（从 1 开始）。

CTID 是 PostgreSQL 的底层机制，它的值在行的生命周期中可能会发生变化。例如，当一个行被更新时，PostgreSQL 会插入一个新的行并删除旧的行，因此新的行会有一个新的 CTID。
在某些情况下，CTID 可以用来快速定位和操纵表中的行。然而，它不应被用作长期行标识符，因为其值可能发生变化。

原文作者: Hi.MogDB

原文链接: https://hi.mogdb.org/posts/b403610d/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议