933
PostgreSQL Oracle 兼容性之 - rowid (CREATE TABLE WITH OIDS)
标签
PostgreSQL , Oracle , 兼容性 , 行号 , rowid , oid , ctid
背景
Oracle的数据中，通过ROWID可以定位到一条记录，当记录没有发生行迁移时，ROWID是不变的，因此即使不使用PK，也能很好的定位到一条记录。
PostgreSQL中，也有行号，CTID，由BLOCK_ID和ITEM_ID组成，即哪个数据块的哪条记录。
但是PostgreSQL的引擎为多版本引擎，因此一条记录在被更新后CTID会发生变化（代表了新的版本）。
不管是Oracle还是PostgreSQL，业务层面都不建议使用行号来唯一标识一条记录。因为Oracle的一些操作也可能产生行迁移，导致ROWID变化。
那么有没有一定不变的字段可以用来标识一条记录呢？
PostgreSQL有多种方法，可以实现这一目的，一个生成后就与该ROW绑定，并永恒不变的ID。
PostgreSQL rowid - sequence 唯一标识
create table tbl (rowid serial8 not null, c1 int, c2 int);
create unique index idx_tbl_1 on tbl(rowid);
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 0 1
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 0 1
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 0 1
postgres=# select * from tbl;
rowid | c1 | c2
-------+----+----
1 | 1 | 2
2 | 1 | 2
3 | 1 | 2
(3 rows)
PostgreSQL rowid - IDENTITY 唯一标识(适用于PostgreSQL 10+)
create table tbl (rowid int8 GENERATED ALWAYS AS IDENTITY not null, c1 int, c2 int);
create unique index idx_tbl_1 on tbl(rowid);
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 0 1
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 0 1
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 0 1
postgres=# select * from tbl;
rowid | c1 | c2
-------+----+----
1 | 1 | 2
2 | 1 | 2
3 | 1 | 2
(3 rows)
PostgreSQL rowid - oids 唯一标识(oid只有32位，记录数超过40亿的单表，不适用)
postgres=# \dT oid
List of data types
Schema | Name | Description
------------+------+-------------------------------------------
pg_catalog | oid | object identifier(oid), maximum 4 billion
(1 row)
例子
postgres=# create table tbl (c1 int, c2 int) with oids;
CREATE TABLE
postgres=# create unique index idx_tbl_oid on tbl(oid);
CREATE INDEX
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 16412 1
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 16413 1
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 16414 1
postgres=# select oid,* from tbl;
oid | c1 | c2
-------+----+----
16412 | 1 | 2
16413 | 1 | 2
16414 | 1 | 2
(3 rows) 最后更新：2017-11-12 02:06:21