PostgreSQL隐藏列(兼容oracle)

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

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

另外oracle的隐藏列有几个注意点:
1、可以将主键、外键都隐藏,但是隐藏之后不能恢复;
2、临时表、外部表、集群表均不支持隐藏列

例子:
–建表插入数据

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列设置为隐藏列

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

–指定列查看和插入数据

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亿,否则可能重复。

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)
发布了101 篇原创文章 · 获赞 35 · 访问量 1万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 像素格子 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览