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