oracle隐含的列,PostgreSQL隐藏列(兼容oracle)

oracle 12cR1新增了隐藏列的功能，在之前的版本中，为了隐藏重要的数据字段以避免在通用查询中显示，我们往往会创建一个视图来隐藏所需信息或应用某些安全条件，现在我们可以通过隐藏列来实现这一功能。https://www.cndba.cn/foucus/article/3985

同时，当我们需要删除一张表的某一列时，如果系统正在允许时直接删除必然会锁表，且数据量大时会锁住很久。这时我们也可以通过隐藏列先将该列隐藏，因为该操作是直接修改数据字典，并没有正在删除数据，等闲时再drop掉该列即可。https://www.cndba.cn/foucus/article/3985https://www.cndba.cn/foucus/article/3985

另外oracle的隐藏列有几个注意点：

可以将主键、外键都隐藏，但是隐藏之后不能恢复；

临时表、外部表、集群表均不支持隐藏列

例子：

—建表插入数据

https://www.cndba.cn/foucus/article/3985

https://www.cndba.cn/foucus/article/3985https://www.cndba.cn/foucus/article/3985

SQL> create table test(id int,id1 int,id2 int,id3 int);

Table created.

SQL> insert into test values(1,2,3,4);

1 row created.

SQL> commit;

Commit complete.

—查看

SQL> desc test;

Name Null? Type

----------------------------------------- -------- ----------------------------

ID NUMBER(38)

ID1 NUMBER(38)

ID2 NUMBER(38)

ID3 NUMBER(38)

SQL> select * from test;

ID ID1 ID2 ID3

---------- ---------- ---------- ----------

1 2 3 4

—将id2列设置为隐藏列

https://www.cndba.cn/foucus/article/3985https://www.cndba.cn/foucus/article/3985

SQL> alter table test modify id2 invisible;

Table altered.

—再次查看和插入数据

SQL> select * from test;

ID ID1 ID3

---------- ---------- ----------

1 2 4

SQL> desc test;

Name Null? Type

----------------------------------------- -------- ----------------------------

ID NUMBER(38)

ID1 NUMBER(38)

ID3 NUMBER(38)

SQL> insert into test values(11,12,13,14);

insert into test values(11,12,13,14)

*

ERROR at line 1:

ORA-00913: too many values

—指定列查看和插入数据

https://www.cndba.cn/foucus/article/3985

SQL> insert into test(id,id1,id2,id3) values(11,12,13,14);

1 row created.

SQL> select id,id1,id2,id3 from test;

ID ID1 ID2 ID3

---------- ---------- ---------- ----------

1 2 3 4

11 12 13 14

那么在pg中隐藏列该如何实现呢？

目前的pg版本中还不支持隐藏列这一功能，将来可能支持，但是我们可以实现类似的功能。

1、使用oid实现隐藏列的功能：

oid为隐藏列。但是最大只能存储40亿条。也就是单表的记录数不能超过40亿，否则可能重复。https://www.cndba.cn/foucus/article/3985

bill=# create table st5 (id int, info text, crt_time timestamp) with (oids);

CREATE TABLE

bill=# alter table st5 add constraint pk_st5 primary key(oid);

ALTER TABLE

bill=# insert into st5 values (1,'a',now());

INSERT 81965 1

bill=# select * from st5;

id | info | crt_time

----+------+---------------------------

1 | a | 2020-01-13 20:55:38.38011

(1 row)

bill=# select oid,* from st5;

oid | id | info | crt_time

-------+----+------+---------------------------

81965 | 1 | a | 2020-01-13 20:55:38.38011

(1 row)

2、使用继承表实现类似功能

实现思路：

pg inherits，继承表增加隐藏字段，真实的数据存储在继承表，主表是用户使用的表(没有隐藏字段)。使用触发器，将数据写入到继承表。

需要注意的是，复制时，解析出来的是继承表的变化，而不是主表。

bill=# create table orig (id int, info text, crt_time timestamp);

CREATE TABLE

bill=# create table hid_orig (id int, info text, crt_time timestamp, hid serial8 primary key) inherits(orig);

NOTICE: merging column "id" with inherited definition

NOTICE: merging column "info" with inherited definition

NOTICE: merging column "crt_time" with inherited definition

CREATE TABLE

bill=# create or replace function tg() returns trigger as $$

bill$# declare

bill$# begin

bill$# insert into hid_orig (id,info,crt_Time) values (new.*);

bill$# return null;

bill$# end;

bill$# $$ language plpgsql strict;

CREATE FUNCTION

bill=# CREATE FUNCTION

bill=# create trigger tg before insert on orig for each row execute procedure tg();

CREATE TRIGGER

bill=# insert into orig values (1,'test',now());

INSERT 0 0

bill=# select * from orig;

id | info | crt_time

----+------+----------------------------

1 | test | 2020-01-13 20:57:12.855603

(1 row)

bill=# select * from hid_orig;

id | info | crt_time | hid

----+------+----------------------------+-----

1 | test | 2020-01-13 20:57:12.855603 | 1

(1 row)

bill=# update orig set info='new' where id=1;

UPDATE 1

bill=# select * from orig;

id | info | crt_time

----+------+----------------------------

1 | new | 2020-01-13 20:57:12.855603

(1 row)

bill=# select * from hid_orig;

id | info | crt_time | hid

----+------+----------------------------+-----

1 | new | 2020-01-13 20:57:12.855603 | 1

(1 row)

版权声明：本文为博主原创文章，未经博主允许不得转载。