背景

本章大纲

1. 数据类型

2. 数据操作

3. 表管理

4. 视图

5. 约束

6. RLS（行安全策略）

第三章：数据定义

1. 数据类型

https://www.postgresql.org/docs/9.6/static/datatype.html

1、数值

NUMERIC(precision, scale)

精度够用时，建议float8，性能比numeric更好。

扩展浮点精度

postgres=# set extra_float_digits=3;  

SET  

2、货币（float8剪切的domain）

3、字符串

长度定义为字符长度，并非字节长度。

4、字节流

输入格式

输出格式

5、日期、时间

6、布尔

7、枚举

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); 

CREATE TABLE person (  

    name text, 

    current_mood mood  

);  

INSERT INTO person VALUES ('Moe', 'happy');  

SELECT * FROM person WHERE current_mood = 'happy'; 

 name | current_mood   

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

 Moe  | happy  

(1 row)  

枚举顺序，与插入顺序一致

8、几何

9、网络

10、比特流

CREATE TABLE test (a BIT(3), b BIT VARYING(5)); 

INSERT INTO test VALUES (B'101', B'00');  

INSERT INTO test VALUES (B'10', B'101');  

ERROR:  bit string length 2 does not match type bit(3)  

INSERT INTO test VALUES (B'10'::bit(3), B'101'); 

SELECT * FROM test;  

  a |  b  

-----+-----  

 101 | 00 

 100 | 101 

11、全文检索

tsvector

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;  

                      tsvector  

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

 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'  

tsquery

SELECT 'fat & rat'::tsquery;  

    tsquery     

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

 'fat' & 'rat'  

SELECT 'fat & (rat | cat)'::tsquery;  

          tsquery            

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

 'fat' & ( 'rat' | 'cat' )  

SELECT 'fat & rat & ! cat'::tsquery;  

        tsquery           

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

 'fat' & 'rat' & !'cat'  

全文检索例子

SELECT to_tsvector( 'postgraduate' ) @@ to_tsquery( 'postgres:*' );  

 ?column? 

----------  

 t  

中文全文检索

https://github.com/jaiminpan/pg_jieba

postgres=# select to_tsvector('jiebacfg','*万岁，如何加快PostgreSQL结巴分词加载速度');    

                                      to_tsvector                                            

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

 'postgresql':6 '万岁':2 '*':1 '分词':8 '加快':5 '加载':9 '结巴':7 '速度':10    

(1 row)    

Time: 0.522 ms    

postgres=# select 8*1000000/14.175527;    

      ?column?           

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

 564352.916120860974    

(1 row)    

Time: 0.743 ms    

12、UUID

create extension "uuid-ossp";  

13、XML

To produce a value of type xml from character data, use the function xmlparse:  

XMLPARSE ( { DOCUMENT | CONTENT } value)  

Examples:  

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')  

XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')  

14、JSON

json内部支持的类型

json和jsonb例子

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;  

                      json                         

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

 {"bar": "baz", "balance": 7.77, "active":false}  

(1 row)  

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;  

                      jsonb                         

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

 {"bar": "baz", "active": false, "balance": 7.77}  

(1 row)  

SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;  

         json          |          jsonb            

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

 {"reading": 1.230e-5} | {"reading": 0.00001230}  

(1 row)  

15、数组

postgres=# select array(select generate_series(1,10)); 

         array            

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

 {1,2,3,4,5,6,7,8,9,10}  

(1 row)  

postgres=# select array['a','b','c'];  

  array   

---------  

 {a,b,c} 

(1 row)  

postgres=# select array['a','b','c'] @> array['a']; 

 ?column?  

----------  

 t  

(1 row)  

数组操作

                                                     List of functions  

   Schema  |          Name           | Result data type |                    Argument data types                    |  Type   

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

 pg_catalog | array_agg               | anyarray         | anyarray                                                 | agg  

 pg_catalog | array_agg               | anyarray         | anynonarray                                               | agg 

 pg_catalog | array_agg_array_finalfn | anyarray         | internal, anyarray                                       | normal  

 pg_catalog | array_agg_array_transfn | internal         | internal, anyarray                                       | normal  

 pg_catalog | array_agg_finalfn       | anyarray         | internal, anynonarray                                     | normal  

 pg_catalog | array_agg_transfn       | internal         | internal, anynonarray                                     | normal 

 pg_catalog | array_append            | anyarray         | anyarray, anyelement                                      | normal  

 pg_catalog | array_cat               | anyarray         | anyarray, anyarray                                        | normal 

 pg_catalog | array_dims              | text             | anyarray                                                 | normal  

 pg_catalog | array_eq                | boolean          | anyarray, anyarray                                        | normal 

 pg_catalog | array_fill              | anyarray         | anyelement, integer[]                                     | normal  

 pg_catalog | array_fill              | anyarray         | anyelement, integer[], integer[]                          | normal 

 pg_catalog | array_ge                | boolean          | anyarray, anyarray                                        | normal  

 pg_catalog | array_gt                | boolean          | anyarray, anyarray                                        | normal 

 pg_catalog | array_in                | anyarray         | cstring, oid, integer                                     | normal  

 pg_catalog | array_larger            | anyarray         | anyarray, anyarray                                        | normal 

 pg_catalog | array_le                | boolean          | anyarray, anyarray                                        | normal  

 pg_catalog | array_length            | integer          | anyarray, integer                                         | normal 

 pg_catalog | array_lower             | integer          | anyarray, integer                                         | normal  

 pg_catalog | array_lt                | boolean          | anyarray, anyarray                                        | normal 

 pg_catalog | array_ndims             | integer          | anyarray                                                 | normal  

 pg_catalog | array_ne                | boolean          | anyarray, anyarray                                        | normal 

 pg_catalog | array_out               | cstring          | anyarray                                                 | normal  

 pg_catalog | array_position          | integer          | anyarray, anyelement                                      | normal 

 pg_catalog | array_position          | integer          | anyarray, anyelement, integer                             | normal  

 pg_catalog | array_positions         | integer[]        | anyarray, anyelement                                      | normal 

 pg_catalog | array_prepend           | anyarray         | anyelement, anyarray                                      | normal  

 pg_catalog | array_recv              | anyarray         | internal, oid, integer                                    | normal 

 pg_catalog | array_remove            | anyarray         | anyarray, anyelement                                      | normal  

 pg_catalog | array_replace           | anyarray         | anyarray, anyelement, anyelement                          | normal 

 pg_catalog | array_send              | bytea            | anyarray                                                 | normal  

 pg_catalog | array_smaller           | anyarray         | anyarray, anyarray                                        | normal 

 pg_catalog | array_to_json           | json             | anyarray                                                 | normal  

 pg_catalog | array_to_json           | json             | anyarray, boolean                                         | normal 

 pg_catalog | array_to_string         | text             | anyarray, text                                            | normal  

 pg_catalog | array_to_string         | text             | anyarray, text, text                                      | normal 

 pg_catalog | array_to_tsvector       | tsvector         | text[]                                                   | normal  

 pg_catalog | array_typanalyze        | boolean          | internal                                                  | normal 

 pg_catalog | array_upper             | integer          | anyarray, integer                                         | normal  

 pg_catalog | arraycontained          | boolean          | anyarray, anyarray                                        | normal 

 pg_catalog | arraycontains           | boolean          | anyarray, anyarray                                        | normal  

 pg_catalog | arraycontjoinsel        | double precision | internal, oid, internal, smallint, internal               | normal 

 pg_catalog | arraycontsel            | double precision | internal, oid, internal, integer                         | normal  

 pg_catalog | arrayoverlap            | boolean          | anyarray, anyarray                                        | normal 

 16、复合类型

CREATE TYPE complex AS (  

    r      double precision,  

    i      double precision  

);  

CREATE TYPE inventory_item AS (  

    name            text,  

    supplier_id     integer, 

    price           numeric  

);  

构造复合类型值

'("fuzzy dice",42,1.99)'  

which would be a valid value of the inventory_item type defined above. To make a field be NULL, write no characters at all in its position in the list.  

'("fuzzy dice",42,)'  

If you want an empty string rather than NULL, write double quotes:  

'("",42,)'  

访问复合类型内的元素

SELECT item.name FROM on_hand WHERE item.price > 9.99;  

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;  

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;  

SELECT (myfunc(x)).* FROM some_table;  

SELECT (myfunc(x)).a, (myfunc(x)).b, (myfunc(x)).c FROM some_table;  

插入、修改复合类型的值

INSERT INTO mytab (complex_col) VALUES((1.1,2.2)); 

UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...; 

UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;  

INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);  

17、范围类型

目前支持的范围类型(用户可以自定义范围类型)

int4range — Range of integer  

int8range — Range of bigint  

numrange — Range of numeric  

tsrange — Range of timestamp without time zone  

tstzrange — Range of timestamp with time zone  

daterange — Range of date  

例子

-- includes 3, does not include 7, and does include all points in between  

SELECT '[3,7)'::int4range;  

-- does not include either 3 or 7, but includes all points in between  

SELECT '(3,7)'::int4range;  

-- includes only the single point 4  

SELECT '[4,4]'::int4range;  

-- includes no points (and will be normalized to 'empty')  

SELECT '[4,4)'::int4range;  

范围类型索引

CREATE INDEX reservation_idx ON reservation USING GIST (during);  

范围类型约束1

CREATE TABLE reservation (  

    during tsrange,  

    EXCLUDE USING GIST (during WITH &&)  

);  

INSERT INTO reservation VALUES  

    ('[2010-01-01 11:30, 2010-01-01 15:00)');  

INSERT 0 1  

INSERT INTO reservation VALUES  

    ('[2010-01-01 14:45, 2010-01-01 15:45)');  

ERROR:  conflicting key value violates exclusion constraint "reservation_during_excl" 

DETAIL:  Key (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")) conflicts  

with existing key (during)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")).  

范围类型约束2 (room相等并且during相交时，排他)

CREATE EXTENSION btree_gist;  

CREATE TABLE room_reservation (  

    room text, 

    during tsrange,  

    EXCLUDE USING GIST (room WITH =, during WITH &&)  

);  

INSERT INTO room_reservation VALUES  

    ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)');  

INSERT 0 1  

INSERT INTO room_reservation VALUES  

    ('123A', '[2010-01-01 14:30, 2010-01-01 15:30)');  

ERROR:  conflicting key value violates exclusion constraint "room_reservation_room_during_excl"  

DETAIL:  Key (room, during)=(123A, ["2010-01-01 14:30:00","2010-01-01 15:30:00")) conflicts  

with existing key (room, during)=(123A, ["2010-01-01 14:00:00","2010-01-01 15:00:00")).  

INSERT INTO room_reservation VALUES  

    ('123B', '[2010-01-01 14:30, 2010-01-01 15:30)');  

INSERT 0 1  

18、对象ID

数据库系统表，大多数使用OID关联

例子

postgres=# select oid::regclass from pg_class limit 10; 

              oid                 

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

 pg_type 

 pg_toast.pg_toast_187550  

 new_type 

 pg_toast.pg_toast_187550_index  

 test  

 pg_toast.pg_toast_187556  

 pg_toast.pg_toast_187556_index  

 tblaccount4 

 pg_toast.pg_toast_187783  

 pg_toast.pg_toast_187783_index  

(10 rows)  

19、PG_LSN(WAL日志地址类型)

wal是PostgreSQL数据库的重做日志, pg_lsn是wal的地址编码类型

postgres=# select pg_current_xlog_insert_location(); 

 pg_current_xlog_insert_location   

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

 43/15D45F48 

(1 row)  

20、虚拟类型(any*)

虚拟类型，比如任意类型，任意数组，任意元素等。编写适合任意类型的动态函数时很有用。

还有一些是用于特殊用途的虚拟类型（触发器，handler等）

例子

postgres=# create or replace function f_test(anyarray) returns anyarray as $$  

select $1;  

$$ language sql strict;  

CREATE FUNCTION  

postgres=# select f_test(array[1,2,3]);  

 f_test   

---------  

 {1,2,3} 

(1 row)  

postgres=# select f_test(array['a','b']);  

 f_test  

--------  

 {a,b}  

(1 row)  

2. 数据操作

https://www.postgresql.org/docs/9.6/static/functions.html

1、函数

每一种类型，都有大量的函数，支持这种类型的计算。

在PostgreSQL中，所有的操作符，索引接口，都是基于函数的，底层都有函数的支撑。

2、操作符

每一种类型，都有大量的操作符，支持这种类型的计算。

每一个操作符，都是通过函数来实现计算的。

如何创建操作符

postgres=# \h create operator  

Command:     CREATE OPERATOR  

Description: define a new operator  

Syntax:  

CREATE OPERATOR name (  

    PROCEDURE = function_name  

    [, LEFTARG = left_type ] [, RIGHTARG = right_type ]  

    [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]  

    [, RESTRICT = res_proc ] [, JOIN = join_proc ]  

    [, HASHES ] [, MERGES ]  

)  

如何查看操作符对应的函数

操作数1 , OP , 操作数2 , 结果, 函数

postgres=# select oprleft::regtype,oprname,oprright::regtype,oprresult::regtype,oprcode::regproc from pg_operator ;  

           oprleft           | oprname |          oprright           |          oprresult          |              oprcode                 

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

 integer                     | =       | bigint                      | boolean                     | int48eq  

 integer                     | <>      | bigint                      | boolean                     | int48ne  

 integer                     | <       | bigint                      | boolean                     | int48lt  

 integer                     | >       | bigint                      | boolean                     | int48gt  

 integer                     | <=      | bigint                      | boolean                     | int48le 

 integer                     | >=      | bigint                      | boolean                     | int48ge  

 boolean                     | <       | boolean                     | boolean                     | boollt  

......  

一元、二元操作符，指操作数的个数

如何查找参数中包含某个特定类型的函数

select proname,proallargtypes::regtype[],proargnames from pg_proc where proallargtypes @> array['integer'::regtype::oid];  

如何查找操作数或结果中包含某个特定类型的操作符

select oprleft::regtype,oprname,oprright::regtype,oprresult::regtype,oprcode::regproc from pg_operator where oprleft='integer'::regtype or oprright='integer'::regtype or oprresult='integer'::regtype;  

 oprleft | oprname | oprright | oprresult |         oprcode            

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

 integer | =       | bigint   | boolean  | int48eq  

 integer | <>      | bigint   | boolean  | int48ne  

 integer | <       | bigint   | boolean  | int48lt  

 integer | >       | bigint   | boolean  | int48gt  

 integer | <=      | bigint   | boolean  | int48le  

 integer | >=      | bigint   | boolean  | int48ge  

 integer | =       | integer  | boolean  | int4eq  

 integer | <       | integer  | boolean  | int4lt  

 xid     | =       | integer  | boolean  | xideqint4  

 xid     | <>      | integer  | boolean  | xidneqint4  

......  

3. 表管理

建表

postgres=#  \h create table  

Command:     CREATE TABLE  

Description: define a new table  

Syntax:  

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [  

  { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]  

    | table_constraint  

    | LIKE source_table [ like_option ... ] }  

    [, ... ] 

] )  

[ INHERITS ( parent_table [, ... ] ) ]  

[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]  

[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] 

[ TABLESPACE tablespace_name ]  

权限管理

设置某个用户，在某个SCHEMA下的所有对象的默认权限

postgres=# \h alter defau  

Command:     ALTER DEFAULT PRIVILEGES  

Description: define default access privileges  

Syntax:  

ALTER DEFAULT PRIVILEGES  

    [ FOR { ROLE | USER } target_role [, ...] ]  

    [ IN SCHEMA schema_name [, ...] ]  

    abbreviated_grant_or_revoke  

where abbreviated_grant_or_revoke is one of:  

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }  

    [, ...] | ALL [ PRIVILEGES ] }  

    ON TABLES 

    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  

将postgres在public里面建立的表，默认赋予select给所有人(PUBLIC)  

postgres=# alter default privileges for role postgres in schema public grant select on tables to public;  

ALTER DEFAULT PRIVILEGES  

postgres=# select * from pg_default_acl ;  

 defaclrole | defaclnamespace | defaclobjtype |   defaclacl     

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

         10 |          181693 | r             | {=r/postgres}  

(1 row)  

将某个schema下的所有表的某个权限赋予给某个用户

postgres=# \h grant  

Command:     GRANT 

Description: define access privileges  

Syntax:  

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }  

    [, ...] | ALL [ PRIVILEGES ] }  

    ON { [ TABLE ] table_name [, ...]  

         | ALL TABLES IN SCHEMA schema_name [, ...] }  

    TO role_specification [, ...] [ WITH GRANT OPTION ]  

将schema public里面的所有表的select权限赋予给所有人(PUBLIC)  

postgres=# grant select on all tables in schema public to public;  

GRANT  

4. 视图

创建

postgres=# \h create view  

Command:     CREATE VIEW  

Description: define a new view  

Syntax:  

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]  

    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]  

    AS query 

    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]  

修改

postgres=# \h alter view  

Command:     ALTER VIEW 

Description: change the definition of a view  

Syntax:  

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression  

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT  

ALTER VIEW [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }  

ALTER VIEW [ IF EXISTS ] name RENAME TO new_name  

ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema 

ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )  

ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )  

普通视图只是QUERY结构，并没有数据，查询时会执行视图中的QUERY。

如果使用视图来隐藏数据，建议对视图使用security_barrier选项，防止使用优化器攻击视图。

digoal=# create table userinfo(id int, groupid int, username text, age int, addr text, email text, phone text);    

CREATE TABLE    

digoal=# insert into userinfo values (1, 1, 'digoal', 1000, '杭州西湖区', 'digoal@126.com', '13999999999');    

INSERT 0 1    

digoal=# insert into userinfo values (2, 1, 'test', 1000, '火星', 'digoal@126.com', '11999999999');    

INSERT 0 1    

digoal=# insert into userinfo values (3, 1, 'test', 1000, '月球', 'digoal@126.com', '11999999999');    

INSERT 0 1    

digoal=# insert into userinfo values (4, 2, 'test', 1000, '土星', 'digoal@126.com', '11999999999');    

INSERT 0 1    

digoal=# create view v_userinfo as select * from userinfo where groupid =2;    

CREATE VIEW    

digoal=# \c digoal digoal    

You are now connected to database "digoal" as user "digoal".    

digoal=> select * from userinfo;    

ERROR:  permission denied for relation userinfo    

digoal=> select * from v_userinfo;    

ERROR:  permission denied for relation v_userinfo    

digoal=> \c digoal postgres    

You are now connected to database "digoal" as user "postgres".    

digoal=# grant select on v_userinfo to digoal;    

GRANT    

digoal=# \c digoal digoal    

You are now connected to database "digoal" as user "digoal".    

digoal=> select * from v_userinfo;    

 id | groupid | username | age  | addr |    email      |    phone       

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

  4 |      2 | test     | 1000 | 土星| digoal@126.com | 11999999999    

(1 row)    

digoal=> create or replace function attack(int,int,text,int,text,text,text) returns boolean as $$    

digoal$> declare    

digoal$> begin    

digoal$>   raise notice '%,%,%,%,%,%,%', $1,$2,$3,$4,$5,$6,$7;    

digoal$>   return true;    

digoal$> end;    

digoal$> $$ language plpgsql cost 0.00000000000000000000001;    

CREATE FUNCTION    

digoal=> select * from v_userinfo;    

 id | groupid | username | age  | addr |    email      |    phone       

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

  4 |      2 | test     | 1000 | 土星| digoal@126.com | 11999999999    

(1 row)    

digoal=> select * from v_userinfo where attack(id,groupid,username,age,addr,email,phone);    

NOTICE:  1,1,digoal,1000,杭州西湖区,digoal@126.com,13999999999    

NOTICE:  2,1,test,1000,火星,digoal@126.com,11999999999    

NOTICE:  3,1,test,1000,月球,digoal@126.com,11999999999    

NOTICE:  4,2,test,1000,土星,digoal@126.com,11999999999    

 id | groupid | username | age  | addr |    email      |    phone       

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

  4 |      2 | test     | 1000 | 土星| digoal@126.com | 11999999999    

(1 row)    

设置视图的安全栅栏属性:     

使用普通的函数就不能攻击他了.    

digoal=> \c digoal postgres    

You are now connected to database "digoal" as user "postgres".    

digoal=# create view v_userinfo_1 with(security_barrier) as select * from userinfo where id=2;    

CREATE VIEW    

digoal=# grant select on v_userinfo_1 to digoal;   

GRANT    

digoal=# \c digoal digoal    

You are now connected to database "digoal" as user "digoal".    

digoal=> select * from v_userinfo_1 where attack(id,groupid,username,age,addr,email,phone);    

NOTICE:  2,1,test,1000,火星,digoal@126.com,11999999999    

 id | groupid | username | age  | addr |    email      |    phone       

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

  2 |      1 | test     | 1000 | 火星| digoal@126.com | 11999999999    

(1 row)    

如果把函数设置为leakproof,就可以被攻击了.(只有超级用户可以创建leakproof函数)    

digoal=> \c digoal postgres    

You are now connected to database "digoal" as user "postgres".    

digoal=# alter function digoal.attack(int,int,text,int,text,text,text) leakproof;    

ALTER FUNCTION    

digoal=# \c digoal digoal    

You are now connected to database "digoal" as user "digoal".    

digoal=> select * from v_userinfo_1 where attack(id,groupid,username,age,addr,email,phone);    

NOTICE:  1,1,digoal,1000,杭州西湖区,digoal@126.com,13999999999    

NOTICE:  2,1,test,1000,火星,digoal@126.com,11999999999    

NOTICE:  3,1,test,1000,月球,digoal@126.com,11999999999    

NOTICE:  4,2,test,1000,土星,digoal@126.com,11999999999    

 id | groupid | username | age  | addr |    email      |    phone       

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

  2 |      1 | test     | 1000 | 火星| digoal@126.com | 11999999999    

(1 row)   

物化视图是带数据的视图。可以对其创建索引。

创建物化视图

postgres=# \h create materialized view  

Command:     CREATE MATERIALIZED VIEW  

Description: define a new materialized view  

Syntax:  

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name 

    [ (column_name [, ...] ) ]  

    [ WITH ( storage_parameter [= value] [, ... ] ) ]  

    [ TABLESPACE tablespace_name ]  

    AS query 

    [ WITH [ NO ] DATA ]  

postgres=# create materialized view mv1 as select * from pg_class;  

<p>SELECT 456</p>  

刷新物化视图数据

postgres=# \h refresh  

Command:     REFRESH MATERIALIZED VIEW  

Description: replace the contents of a materialized view 

Syntax:  

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name  

    [ WITH [ NO ] DATA ]  

必须有PK或者UK，才能增量刷新物化视图  

postgres=# refresh materialized view concurrently mv1; 

ERROR:  cannot refresh materialized view "public.mv1" concurrently  

HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view. 

postgres=# create unique index uk_mv1 on mv1(relname); 

CREATE INDEX  

postgres=# refresh materialized view concurrently mv1; 

REFRESH MATERIALIZED VIEW  

5.约束

1、主外键、唯一约束

reference key必须是唯一约束字段或PK字段。

CREATE TABLE

postgres=# create table ftbl(id int, c1 int references rtbl(id), info text);

CREATE TABLE

postgres=# \d+ rtbl

Table "public.rtbl"

Column |  Type   | Modifiers | Storage  | Stats target | Description

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

id     | integer | not null  | plain    |              |

info   | text    |           | extended |              |

Indexes:

"rtbl_pkey" PRIMARY KEY, btree (id)

Referenced by:

TABLE "ftbl" CONSTRAINT "ftbl_c1_fkey" FOREIGN KEY (c1) REFERENCES rtbl(id)

postgres=# \d+ ftbl

Table "public.ftbl"

Column |  Type   | Modifiers | Storage  | Stats target | Description

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

id     | integer |           | plain    |              |

c1     | integer |           | plain    |              |

info   | text    |           | extended |              |

Foreign-key constraints:

"ftbl_c1_fkey" FOREIGN KEY (c1) REFERENCES rtbl(id)

2、check约束

CREATE TABLE

postgres=# insert into cktbl values (1,'test');;

ERROR:  new row for relation "cktbl" violates check constraint "cktbl_id_check"

DETAIL:  Failing row contains (1, test).

3、排他约束

during tsrange,

EXCLUDE USING GIST (during WITH &&)

);

INSERT INTO reservation VALUES

('[2010-01-01 11:30, 2010-01-01 15:00)');

INSERT 0 1

INSERT INTO reservation VALUES

('[2010-01-01 14:45, 2010-01-01 15:45)');

ERROR:  conflicting key value violates exclusion constraint "reservation_during_excl"

DETAIL:  Key (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")) conflicts

with existing key (during)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")).

4、约束判定时机

https://www.postgresql.org/docs/9.6/static/sql-set-constraints.html

https://www.postgresql.org/docs/9.6/static/sql-createtable.html

约束定义层面的设置, 参考alter table, create table语法。

是否允许延迟判定约束:

NOT DEFERRABLE

如果配置了允许延迟判定是否违反约束，那么什么时候判定？

INITIALLY DEFERRED   -- 事务结束时判定

事务中设置(覆盖约束的定义设置)

6. RLS（行安全策略）

行安全策略有利于隔离控制共享表在多个用户之间的数据呈现和使用.

实现方法,

创建针对表和角色的策略, 不同的角色对表记录的查询, 插入, 更新, 删除可以有不同的控制方法.

Command:     CREATE POLICY

Description: define a new row level security policy for a table

Syntax:

CREATE POLICY name ON table_name

[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]

[ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]

[ USING ( using_expression ) ]

[ WITH CHECK ( check_expression ) ]

using 针对已经存在的记录的校验. 可实施在select, update, delete, ALL上.

whth check 针对将要新增的记录的校验, 可实施在insert, update, ALL上.

需要注意的是, UPDATE因为涉及旧的记录和新的记录, 如果只写了using , 但是没有提供with check的话, using同时会当成with check来使用进行检查.

如果针对同样的命令创建了多个策略, 所有策略中任意一个为TRUE都通过.

例如ALL, SELECT个创建了一个策略for role r1, 执行select时任意一个为TRUE都通过.

例如SELECT个创建了多个策略for role r1, 执行select时任意一个为TRUE都通过.

例子

创建三个角色

CREATE ROLE

postgres=# create role r2 login;

CREATE ROLE

postgres=# create role r3 login;

CREATE ROLE

创建测试表

CREATE TABLE

postgres=# insert into test values(1, 'r1');

INSERT 0 1

postgres=# insert into test values(2, 'r2');

INSERT 0 1

postgres=# insert into test values(3, 'r3');

INSERT 0 1

postgres=# grant all on table test to public;

GRANT

创建一个新增数据的策略(使用with check)

CREATE POLICY

默认情况下策略是disable状态的,

Table "public.test"

Column |  Type   | Modifiers | Storage | Stats target | Description

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

id     | integer |           | plain   |              |

r      | name    |           | plain   |              |

Policies (Row Security Disabled):

POLICY "p" FOR INSERT

TO r1

WITH CHECK (r = "current_user"())

通过pg_policies视图可以查看已经创建的策略.

schemaname | tablename | policyname | roles |  cmd   | qual |       with_check

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

public     | test      | p          | {r1}  | INSERT |      | (r = "current_user"())

(1 row)

在策略enable前, 是无视策略的.

INSERT 0 1

postgres=> insert into test values(4,'r2');

INSERT 0 1

使策略生效

ALTER TABLE

postgres=> \d+ test

Table "public.test"

Column |  Type   | Modifiers | Storage | Stats target | Description

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

id     | integer |           | plain   |              |

r      | name    |           | plain   |              |

Policies:

POLICY "p" FOR INSERT

TO r1

WITH CHECK (r = "current_user"())

策略生效后, 再次插入, 你会看到只能插入和r1角色同名的r值.

You are now connected to database "postgres" as user "r1".

postgres=> insert into test values(4,'r2');

ERROR:  new row violates WITH CHECK OPTION for "test"

postgres=> insert into test values(4,'r1');

INSERT 0 1

再新增一个策略, 现在r1角色插入test表时, 允许r字段的值为'r1','r2'.

CREATE POLICY

postgres=# \c postgres r1

You are now connected to database "postgres" as user "r1".

postgres=> insert into test values(4,'r2');

INSERT 0 1

postgres=> insert into test values(4,'r1');

INSERT 0 1

postgres=> insert into test values(4,'r3');

ERROR:  new row violates WITH CHECK OPTION for "test"

创建旧值策略(using).让r1用户只能查看到r=current_user的值.

You are now connected to database "postgres" as user "postgres".\

postgres=# create policy p2 on test for select to r1 using ( r = current_user);

CREATE POLICY

postgres=# \c postgres r1

You are now connected to database "postgres" as user "r1".

postgres=> select * from test;

id | r

----+----

1 | r1

4 | r1

4 | r1

4 | r1

(4 rows)

创建一个针对所有用户的策略,例如, 所有用户只能看到r = current_user 的值.

You are now connected to database "postgres" as user "postgres".

postgres=# create policy p3 on test for select to public using ( r = current_user);

CREATE POLICY

postgres=# \c postgres r2

You are now connected to database "postgres" as user "r2".

postgres=> select * from test;

id | r

----+----

2 | r2

4 | r2

4 | r2

(3 rows)

注意，这些策略只针对非超级用户以及非table owner

postgres=> \c postgres postgres    
You are now connected to database "postgres" as user "postgres".    
postgres=# select * from test;    
 id | r      
----+----    
  1 | r1    
  2 | r2    
  3 | r3    
  4 | r1    
  4 | r2    
  4 | r1    
  4 | r2    
  4 | r1    
(8 rows)    

把r1改为超级用户, 策略失效.

postgres=# alter role r1 superuser;    
ALTER ROLE    
postgres=# \c postgres r1    
You are now connected to database "postgres" as user "r1".    
postgres=# select * from test;    
 id | r      
----+----    
  1 | r1    
  2 | r2    
  3 | r3    
  4 | r1    
  4 | r2    
  4 | r1    
  4 | r2    
  4 | r1    
(8 rows)    

对于update操作, 因为先需要查看数据, 然后才是插入数据, 所以先会执行using检查, 然后执行with check检查. 如果只有using, 那么with check还是需要检查的, 只不过会使用using策略.

如果只有with check则在查询数据时不检查, 但是插入时检查.

注意，一旦对用户创建了策略,必须在所有命令(insert,update,delete,select)上创建, 否则默认采用拒绝方式.

例如, 现在有1个update的策略.

postgres=# \d test    
     Table "public.test"    
 Column |  Type   | Modifiers     
--------+---------+-----------    
 id     | integer |     
 r      | name    |     
Policies:    
    POLICY "p4" FOR UPDATE    
      TO r3    
      USING (r = "current_user"())    
postgres=# \c postgres r3    
You are now connected to database "postgres" as user "r3".    

因为针对r3角色创建了update策略, 但是没有创建其他命令的策略, 所以其他命令的策略默认为FALSE

postgres=> select * from test;    
 id | r     
----+---    
(0 rows)    

更新操作应用了策略.

postgres=> update test set id=4 where r='r3';    
UPDATE 1    
postgres=> select * from test;    
 id | r     
----+---    
(0 rows)    

现在创建SELECT的策略, 可以查询了

postgres=# create policy p1 on test for select to r3 using ( r = current_user);    
CREATE POLICY    
postgres=# \d+ test    
                         Table "public.test"    
 Column |  Type   | Modifiers | Storage | Stats target | Description     
--------+---------+-----------+---------+--------------+-------------    
 id     | integer |           | plain   |              |     
 r      | name    |           | plain   |              |     
Policies:    
    POLICY "p1" FOR SELECT    
      TO r3    
      USING (r = "current_user"())    
    POLICY "p4" FOR UPDATE    
      TO r3    
      USING (r = "current_user"())    
postgres=# \c postgres r3    
You are now connected to database "postgres" as user "r3".    
postgres=> select * from test;    
 id | r      
----+----    
  4 | r3    
(1 row)    

但是delete命令上还没有创建策略, 所以删除操作直接FALSE.

postgres=> delete from test ;    
DELETE 0    

在r1角色上, 没有创建任何策略, 所以操作是允许的.

postgres=> \c postgres r1    
You are now connected to database "postgres" as user "r1".    
postgres=# select * from test;    
 id | r      
----+----    
  1 | r1    
  2 | r2    
  4 | r1    
  4 | r2    
  4 | r1    
  4 | r2    
  4 | r1    
  4 | r3    
(8 rows)