Postgresql逻辑结构整理

数据库：

创建数据库：

create database name

[with owner = user_name --指定新建数据库属于哪个用户，不指定则属于当前执行命令的用户；

template = template --模板名，不指定则默认使用 template1.

encoding = encoding --数据库使用的字符编码，  编码与区域设置必须与模板相一致。template0公认不包含任何受字符集编码或排序影响的数据或索引，故可作为任意字符集数据库的模板。

lc_collate = lc_collate

lc_ctype - lc_ctype

tablespace tablespace --指定和新数据库关联的表空间的名字

connection limit = connlimit] -数据库可以接受多少并发连接数，默认为-1，表示没有限制。

日常使用中很少指定数据库的字符集，因为Postgresql数据库服务端不支持通常的字符集“GBK”,“GB18030”,所以一般都使用“UTF8”字符集来支持中文

 

修改数据库：

ALTER DATABASE name rename TO new_name;

ALTER DATABASE name owner TO new_owner;

ALTER DATABASE name SET tablespase new_tablespace;

ALTER DATABASE name SET configuration_parameter{TO|=}{value|DEFAULT};

ALTER DATABASE name SET configuration_parameter FROM CURRENT;

ALTER DATABASE name RESET configuration_parameter;

ALTER DATABASE name RESET ALL;

 

--修改数据库最大连接数

postgres=# alter database testdb2 connection limit 100;

ALTER DATABASE

postgres=#

 

drop database [IF EXISTS] name ;

如果还有人连接该库，将不能删除；

 

常见问题：

1.能否在事务块中创建或删除数据库？

答：不能

 

2.能否在数据库中修改数据库

答：能

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

模式

模式（schema）：可理解为一个命名空间或目录，不同的模式下可以有相同名称的表、函数等对象且不冲突。

允许多个用户在使用同一个数据库时彼此互不干扰；

把数据库对象放在不同模式下，然后组织成逻辑组，便于管理；

第三方的应用可以放在不同的模式中，就不会和其他对象名字冲突了。

--创建

CREATE SCHEMA schemaname ;

postgres=# \c testdb2;

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

testdb2=# \dn;

  List of schemas

  Name  |  Owner   

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

 public | postgres

(1 row)

 

testdb2=# create schema test;

CREATE SCHEMA

testdb2=# \dn

  List of schemas

  Name  |  Owner   

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

 public | postgres

 test   | postgres

(2 rows)

 

testdb2=#

--删除

postgres=# drop schema test_schema;

DROP SCHEMA

postgres=#

--下面的命令为用户osdba建的模式，名字也定为osdba;

 

create schema authorization osdba;

 

公共模式

schema_name.table_name;

通常创建和访问表的时候都不用指定模式，默认访问的是“public”模式。

 

模式搜索路径

public模式总是在搜索路径中，所以public为建表的默认模式。

testdb2=# show search_path;

   search_path   

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

 "$user", public

(1 row)

 

模式权限

默认情况下，用用户无法访问不属于他们的对象。若要访问，模式的所有者必须在模式上赋予他们“USAGE”权限。为了让用户使用模式中的对象，可能需要赋予适合该对象的额外权限。

 

默认情况下，每个人在“public”模式上都有“create"和”usage“权限。如果撤销该权限：

revoke create on schema public from PUBLIC;

第一个public是模式名称，第二个PUBLIC是所有用户意思。

 

模式移植性：

因其它数据库没有模式概念，需考虑数据库的兼容；

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

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

表

主键约束

一般的表都有主键，如果由一个字段组成，直接在字段定义后加上“primary key”关键字；

create table test1(id int primary key,note varchar(20));

如果为两个以上字段，则为复合主键，语法为：

create table test02(id1 int, id2 int, note vachar(20), CONSTRAINT pk_test02 primary key(id1,id2));

唯一约束

create table test03(id1 int, id2 int,id3 int, note vachar(20), CONSTRAINT pk_test03 primary key(id1,id2),CONSTRAINT uk_test03_id3 UNIQUE(id3));

检查约束

create table child(name varchar(20),age int, note text, CONSTRAINT ch_child_age CHECK(age >0 and age<18));

 

复杂表结构：

create table baby(like child);

注意：没有把源表上的约束复制过来，如果要复制，需加上“INCLUDING”,可用的“INCLUDES”选项为：

INCLUDING DEFAULT

INCLUDING CONSTRAINT

INCLUDING INDEXES

INCLUDING STORAGE

INCLUDING COMMENTS

INCLUDING ALL

 

也可用create table as 来建表

create table baby as select * from child with no data;

 

临时表：

会话级临时表：会话的生命周期

 

事务级临时表：事务的生命周期

 

testdb2=# create temporary table tmp_t1(id int primary key, note text);

CREATE TABLE

testdb2=# \d

            List of relations

  Schema   |   Name    | Type  |  Owner   

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

 pg_temp_7 | tmp_t1    | table | postgres

 public    | card_test | table | postgres

 public    | date_test | table | postgres

 public    | jtest01   | table | postgres

 public    | jtest02   | table | postgres

 public    | jtest03   | table | postgres

 public    | t_number  | table | postgres

 public    | t_user    | table | postgres

 public    | test      | table | postgres

 public    | testtab05 | table | postgres

 public    | testtab6  | table | postgres

(11 rows)

临时表在schema下生成一个特殊的表，这个schema的名称为pg_tmp_XX","XX"代表一个数字，不同的sseion这个数字是不同的。

 

testdb2=# insert into tmp_t1 values(1,'111');

INSERT 0 1

testdb2=# insert into tmp_t1 values(2,'122');

INSERT 0 1

testdb2=# select * from tmp_t1;

 id | note

----+------

  1 | 111

  2 | 122

(2 rows)

默认情况下，创建的临时表是会话级别的，如果想创建事务级的临时表，可加上“ON COMMIT DELETE ROWS”

 

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

默认值

字段设置默认值

testdb2=# create table student(no int, name varchar(20),age int default 15);

CREATE TABLE

testdb2=# insert into student (no,name) values(1,'张三');

INSERT 0 1

testdb2=# insert into student (no,name) values(2,'张将');

INSERT 0 1

testdb2=# select * from student;

 no | name | age

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

  1 | 张三 |  15

  2 | 张将 |  15

(2 rows)

 

--可使用DEFAULT来代替默认值。如果没有声明默认值，则为null.

testdb2=# update student set age = 16;

UPDATE 2

testdb2=# select * from student;

 no | name | age

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

  1 | 张三 |  16

  2 | 张将 |  16

(2 rows)

 

testdb2=# update student set age = DEFAULT where no = 2;

UPDATE 1

testdb2=# select * from student;

 no | name | age

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

  1 | 张三 |  16

  2 | 张将 |  15

(2 rows)

 

testdb2=# create table blog(id int ,title text,created_date timestamp default now());

CREATE TABLE

testdb2=# insert into blog values(1,'Postgresql创建临时表');

INSERT 0 1

testdb2=# select * from blog;

 id |        title         |        created_date        

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

  1 | Postgresql创建临时表 | 2017-10-07 18:20:47.706399

(1 row)

 

testdb2=#

 

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

约束

 

（1）检查约束

create table persons(

name varchar(40),

age int CHECK(age >=0 and age <=150),

sex boolean

);

 

create table books(

book_no integer,

name text,

price numeric CHECK (price >0),

discounted_price numeric CHECK(discounted_price > 0), --字段约束

CHECK(price > discounted_price) --表约束

);

（2）非空约束

create table books(

book_no integer not null,

name text,

price numeric

);

（3）唯一约束

create table books(

book_no integer unique,

name text,

price numeric

);

（4）外键约束

表之间关系的一种约束，参照完整性。

create table class(

class_no int primary key,

class_name varchar(40)

);

create table student(

student_no int primary key,

student_name varchar(40),

age int,

class_no int references class(class_no)

);

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

修改表：

增加字段：

alter table class add column class_teacher varchar(40);

alter table class add column class_teacher varchar(40) CHECK (class_teacher <> '');

删除字段：

alter table class drop column class_teacher;

alter table class drop column class_no CASCADE;--会把引用class_no的外键也删除掉

增加约束：

alter table student add CHECK(age < 19);

alter table class ADD CONSTRAINT unique_class_teacher UNIQUE (class_teacher);

alter table student alter column student_name set not null; --非空约束

删除约束：

alter table student drop CONSTRAINT constraint_name; --删除前需要知道约束名称，一般用 “\d”把约束名称查出来，再删除；

修改默认值：

alter table student alter column set default 13;--不会影响现有的行数据，只将将来的insert命令改变默认值。

删除默认值：

alter table student alter column age drop default;

修改字段类型：

alter table student alter column student_name TYPE text;--只有在字段里现有的每个项都可以隐式地转换成新类型时，才能成功；

重命名字段：

alter table books RENAME COLUMN book_no TO book_id;

重命名表：

alter table class RENAME to classes;

 

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

表的继承

create table persons(

name text,

age int,

sex boolean

);

create table students(

class_no int

)INHERITS(persons);

insert into students values('张三',15,true,1);

insert into students values('张花',16,false,2);

select * from persons;

postgres=# select * from persons;

 name | age | sex

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

 张三 |  15 | t

 张花 |  16 | f

(2 rows)

postgres=# select * from students;

 name | age | sex | class_no

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

 张三 |  15 | t   |        1

 张花 |  16 | f   |        2

(2 rows)

--更改students表中数据，通过persons表也可看到变化：

update students set age = 13 where name='张三';

postgres=# update students set age = 13 where name='张三';

UPDATE 1

postgres=# select * from persons;

 name | age | sex

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

 张花 |  16 | f

 张三 |  13 | t

(2 rows)

--往persons表中插入一条数据，查询student表是看不到这条数据的；

查询父表会把子表的数据显示出来，反过来不行

如果想查询父表的数据，在表名前加上only

所有父表的检查约束和非空约束会自动被子表继承，其它类型的约束不会

一个子表可以从多个父表继承，它将拥有所有父表字段的总和，并且子表中的字段也会加入。

如果一个字段出现在多个父表，或出现在父表和子表中，这些字段将进行融合，只保留一个。

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

分区表

表分区就是把逻辑上的一个大表分割成物理上的几个小块。分区可提供若干好处：

1.删除历史数据，通过分区  快；

2.查询同一个分区内数据   快；

3.用到较少的历史数据通过分区存放到其它物理介质上。

 

什么时候使用分区表？表的大小超过了数据库服务器物理内存的大小

使用继承实现分区表时，一般会让父表为空，数据存储在子表中。

建分区表步骤：

1.创建父表，所有分区都从它继承。这个表没有数据，没有任何检查约束，除非想约束所有的分区。

2.创建几个子表，每个都是从主表上继承的，通常不会增加任何字段，我们把这些子表称为分区，实际上它们就是普通的Postgresl表。

3.给分区表增加约束，定义每个分区允许的键值

4.对于每个分区，在关键字字段上创建一个索引，和想创建的其它索引。如果关键字唯一，则创建唯一约束或主键约束。

5.定义一个规则或触发器，把对主表的数据插入重定向到合适的分区表。

6.确保constraint_exclusion里的配置参数postgresql.conf是打开的。打开后，如果查询中where子句的过滤条件与分区的约束条件匹配，则这个查询只查询这个分区，而不会查询其它分区。

 

分区表示例：

假设一张销售明细表：

create table sales_detail(

product_id int not null,

price numeric(12,2),

amount int not null,

sale_date date not null,

buyer varchar(40),

buyer_contact text

);

建分区表：

create table sales_detail_y2017m07 (check (sale_date >= DATE '2017-07-01' and sale_date < DATE '2017-08-01')) inherits(sales_detail);

create table sales_detail_y2017m08 (check (sale_date >= DATE '2017-08-01' and sale_date < DATE '2017-09-01')) inherits(sales_detail);

create table sales_detail_y2017m09 (check (sale_date >= DATE '2017-09-01' and sale_date < DATE '2017-10-01')) inherits(sales_detail);

create table sales_detail_y2017m10 (check (sale_date >= DATE '2017-10-01' and sale_date < DATE '2017-11-01')) inherits(sales_detail);

分区表上建的检查约束，只允许插入本月数据；

一般情况下，还可以在分区键“sale_date”上建索引：

create index sale_detail_y2017m07_sale_date ON sales_detail_y2017m07 (sale_date);

create index sale_detail_y2017m08_sale_date ON sales_detail_y2017m08 (sale_date);

create index sale_detail_y2017m09_sale_date ON sales_detail_y2017m09 (sale_date);

create index sale_detail_y2017m10_sale_date ON sales_detail_y2017m10 (sale_date);

 

如果有需要，还可以在其它字段上建索引。

 

自动且正确的把数据插入到正确的分区：使用触发器

create or replace function sale_detail_insert_trigger()

RETURNS TRIGGER AS $$

begin

if(NEW.sale_date >= DATE'2017-07-01' and NEW.sale_date < DATE '2017-08-01') then

insert into sales_detail_y2017m07 values(NEW.*);

elsif(NEW.sale_date >= DATE'2017-08-01' and NEW.sale_date < DATE '2017-09-01') then

insert into sales_detail_y2017m08 values(NEW.*);

elsif(NEW.sale_date >= DATE'2017-09-01' and NEW.sale_date < DATE '2017-10-01') then

insert into sales_detail_y2017m09 values(NEW.*);

elsif(NEW.sale_date >= DATE'2017-10-01' and NEW.sale_date < DATE '2017-11-01') then

insert into sales_detail_y2017m10 values(NEW.*);

else

raise exception 'Date out of range. Fix the sale_detail_insert_trigger() function!';

END IF;

RETURN NULL;

end;

$$

language plpgsql;

 

create trigger insert_sale_detail_trigger

before insert on sales_detail

for each row execute procedure sale_detail_insert_trigger();

 

 

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

该部分是因为建的触发器有问题

testdb2=# insert into sales_detail values(1,43.12,1,date'20170-09-14','四远方','杭州钱塘江区小碗城');

ERROR:  Date out of range. Fix the sale_detail_insert_trigger() function!

CONTEXT:  PL/pgSQL function sale_detail_insert_trigger() line 12 at RAISE

testdb2=# insert into sales_detail values(1,43.12,1,date'2017-09-14','四远方','杭州钱塘江区小 碗城');

ERROR:  "sale_detail_y2017m09_sale_date" is an index

LINE 1: insert into sale_detail_y2017m09_sale_date values(NEW.*)

                    ^

QUERY:  insert into sale_detail_y2017m09_sale_date values(NEW.*)

CONTEXT:  PL/pgSQL function sale_detail_insert_trigger() line 8 at SQL statement

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

testdb2=# insert into sales_detail values(1,43.12,1,date'2017-09-14','四远方','杭州钱塘江区小 碗城');

INSERT 0 0

testdb2=#

testdb2=# insert into sales_detail values(13,56.02,31,date'2017-10-14','王思聪','上海浦东新区万达大酒店');

INSERT 0 0

testdb2=#

testdb2=# insert into sales_detail values(13,56.02,31,date'2017-11-14','王思聪','上海浦东新区万达大酒店');

ERROR:  Date out of range. Fix the sale_detail_insert_trigger() function!

CONTEXT:  PL/pgSQL function sale_detail_insert_trigger() line 12 at RAISE

testdb2=#

 

testdb2=# select * from sales_detail ;

 product_id | price | amount | sale_date  | buyer  |     buyer_contact      

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

          1 | 43.12 |      1 | 2017-09-14 | 四远方 | 杭州钱塘江区小碗城

         13 | 56.02 |     31 | 2017-10-14 | 王思聪 | 上海浦东新区万达大酒店

(2 rows

 

分区的优化技巧：

打开约束排除（constraint_exclusion）是一种查询优化技巧；

在sql查询中将where语句中的过滤条件与表上的check条件进行对比，就可得知不需要扫描的分区，从而跳过相应的分区表，性能也得到提高，如下：

testdb2=# explain select count(*) from sales_detail where sale_date >= Date'2017-09-01';

                                     QUERY PLAN                                     

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

 Aggregate  (cost=33.04..33.05 rows=1 width=8)

   ->  Append  (cost=0.00..32.29 rows=300 width=0)

         ->  Seq Scan on sales_detail  (cost=0.00..1.54 rows=14 width=0)

               Filter: (sale_date >= '2017-09-01'::date)

         ->  Seq Scan on sales_detail_y2017m09  (cost=0.00..15.38 rows=143 width=0)

               Filter: (sale_date >= '2017-09-01'::date)

         ->  Seq Scan on sales_detail_y2017m10  (cost=0.00..15.38 rows=143 width=0)

               Filter: (sale_date >= '2017-09-01'::date)

(8 rows)

 

如果把参数 "constraint_exclusion"设置成off,则会扫描每张分区子表，如下：

testdb2=# set constraint_exclusion='off';

SET

testdb2=# explain select count(*) from sales_detail where sale_date >= Date'2017-09-01';

                                     QUERY PLAN                                     

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

 Aggregate  (cost=64.50..64.51 rows=1 width=8)

   ->  Append  (cost=0.00..63.04 rows=586 width=0)

         ->  Seq Scan on sales_detail  (cost=0.00..1.54 rows=14 width=0)

               Filter: (sale_date >= '2017-09-01'::date)

         ->  Seq Scan on sales_detail_y2017m07  (cost=0.00..15.38 rows=143 width=0)

               Filter: (sale_date >= '2017-09-01'::date)

         ->  Seq Scan on sales_detail_y2017m08  (cost=0.00..15.38 rows=143 width=0)

               Filter: (sale_date >= '2017-09-01'::date)

         ->  Seq Scan on sales_detail_y2017m09  (cost=0.00..15.38 rows=143 width=0)

               Filter: (sale_date >= '2017-09-01'::date)

         ->  Seq Scan on sales_detail_y2017m10  (cost=0.00..15.38 rows=143 width=0)

               Filter: (sale_date >= '2017-09-01'::date)

(12 rows)

 

testdb2=#

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

 

触发器

是一种由事件自动触发执行的特殊的存储过程，这些事件可以是insert,update,delete等。

经常用于加强数据的完整性约束和业务规则上的约束。

 

建触发器步骤

先为触发器建一个执行函数，此函数的返回类型为触发器类型；然后即可创建触发器。

 

CREATE [CONSTRAINT] TRIGGER name {BEFORE | AFTER | INSTEAD OF} {EVENT [OR ...]} ON table_name

[FROM referenced_table_name]

{NOT DEFERRABLE | [DEFERRABLE] {INITIALLY IMMEDIATE | INITIALLY DEFERRED}}

[FOR [EACH] {ROW | STATEMENT}]

[WHEN (condition)]

EXECUTE PROCEDURE function_name (arguments)

 

示例：

create table student_info(

student_no int primary key,

student_name varchar(40),

age int

);

create table score_info(

student_no int,

chinese_score int,

math_score int,

test_date date

);

 

如果想删除学生表中一条记录时，把学生在成绩表（score_info)中的成绩记录也删除，就可以使用触发器。先建触发器的执行函数：

CREATE OR REPLACE FUNCTION student_delete_trigger()

RETURNS TRIGGER AS $$

begin

delete from score_info where student_no = OLD.student_no;

return OLD;

end;

$$

language plpgsql;

再创建触发器：

create trigger delete_student_trigger

after delete on student_info

for each row execute procedure student_delete_trigger();

插入测试数据：

insert into student_info values(1,'xx狗1',14);

insert into student_info values(2,'xx狗2',15);

insert into student_info values(3,'xx狗3',16);

insert into student_info values(4,'xx狗4',17);

 

insert into score_info values(1,84,75,date'2017-05-22');

insert into score_info values(1,81,73,date'2017-06-22');

insert into score_info values(2,87,75,date'2017-07-22');

insert into score_info values(2,64,85,date'2017-04-22');

insert into score_info values(3,94,25,date'2017-05-22');

insert into score_info values(3,55,88,date'2017-07-22');

insert into score_info values(4,44,53,date'2017-09-22');

insert into score_info values(4,23,83,date'2017-04-22');

 

把学生好为3的学生删除掉：

testdb2=# delete from student_info where student_no = 3;

DELETE 1

再查下score_info表：              ^

testdb2=# select * from score_info ;

 student_no | chinese_score | math_score | test_date  

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

          1 |            84 |         75 | 2017-05-22

          1 |            81 |         73 | 2017-06-22

          2 |            87 |         75 | 2017-07-22

          2 |            64 |         85 | 2017-04-22

          4 |            44 |         53 | 2017-09-22

          4 |            23 |         83 | 2017-04-22

(6 rows)

 

testdb2=#

 

 

语句级触发器指执行每个sql时，只执行一次；

行级触发器指每行就会执行一次；

一个修改0行的操作仍会导致语句级触发器被执行。

 

create table log_student(

update_time timestamp,--操作时间

db_user varchar(40), --操作的数据库用户名

opr_type varchar(6)  --操作类型：insert、delete、update

);

创建记录log的触发器函数，如下：

create function log_student_trigger()

returns trigger as

$$

begin

insert into log_studnet values(now(),user,TG_OP);

return null;

end;

$$

language "plpgsql";

 

在student上创建一个语句级触发器：

create trigger log_student_trigger

after insert or delete or update on student

for statement execute procedure log_student_trigger();

testdb2=# insert into student values(1,'张生',14),(2,'水生','25');

INSERT 0 2

testdb2=# select * from log_student;

        update_time         | db_user  | opr_type

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

 2017-10-11 13:18:46.732591 | postgres | INSERT

(1 row)

 

testdb2=#

从上可看出：虽然插入了2行，但只有一条语句，所以在log_studnet上只记录了一次操作。

testdb2=# update student set age = 18;

UPDATE 4

testdb2=# select * from log_student;

        update_time         | db_user  | opr_type

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

 2017-10-11 13:18:46.732591 | postgres | INSERT

 2017-10-11 13:21:22.631776 | postgres | UPDATE

(2 rows)

 

testdb2=

虽然update更新了4行，但log_student只记录了一条记录。语句级触发器是按语句进行触发的，不管实际操作了多少行。

下面的更新，实际没有更新任何数据，也会被触发：

testdb2=# update student set age = 19 where no = 3333;

UPDATE 0

testdb2=# select * from log_student;

        update_time         | db_user  | opr_type

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

 2017-10-11 13:18:46.732591 | postgres | INSERT

 2017-10-11 13:21:22.631776 | postgres | UPDATE

 2017-10-11 13:24:13.225804 | postgres | UPDATE

(3 rows)

 

testdb2=#

行级触发器：

先清掉log_student表；

testdb2=# delete from log_student;

DELETE 3

testdb2=#

 

create trigger log_student_trigger2

after insert or delete or update on student

for row execute procedure log_student_trigger();

插入数据：

testdb2=# insert into student values(11,'张生',14),(22,'水生','25');

INSERT 0 2

testdb2=# select * from log_student;

        update_time         | db_user  | opr_type

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

 2017-10-11 13:42:03.097678 | postgres | INSERT

 2017-10-11 13:42:03.097678 | postgres | INSERT

 2017-10-11 13:42:03.097678 | postgres | INSERT

(3 rows)

插入了2条数据，应该是2条日志才对，但实际得到了3条，不知道怎么回事。

 

更改数据：

testdb2=# update student set age = 19 ;

UPDATE 6

testdb2=# select * from log_student;

        update_time         | db_user  | opr_type

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

 2017-10-11 13:42:03.097678 | postgres | INSERT

 2017-10-11 13:42:03.097678 | postgres | INSERT

 2017-10-11 13:42:03.097678 | postgres | INSERT

 2017-10-11 13:44:36.990037 | postgres | UPDATE

 2017-10-11 13:44:36.990037 | postgres | UPDATE

 2017-10-11 13:44:36.990037 | postgres | UPDATE

 2017-10-11 13:44:36.990037 | postgres | UPDATE

 2017-10-11 13:44:36.990037 | postgres | UPDATE

 2017-10-11 13:44:36.990037 | postgres | UPDATE

 2017-10-11 13:44:36.990037 | postgres | UPDATE

(10 rows)

 

testdb2=#

更改了6条，却又7条更改记录。

 

删除触发器：

drop trigger [if exists] name on table [cascade | restrict];

 

if exists:如果指定的触发器不存在，则发出一个notice而不是抛出一个错误。

cascade:级联操作依赖此触发器的对象

restirct:默认值，如果有依赖对象的存在，拒绝删除；

 

drop trigger user_new_name_student_trigger on student;

 

删除触发器时，触发器的函数不会被删除；

当表删除时，表上的触发器也会被删除；

 

触发器函数都有返回值，语句级触发器应该总返回null,即必须显式地在触发器函数中写上“return null”,如果没有写，将报错。

触发器函数中的特殊变量：

NEW：该变量为insert/update操作触发的行级触发器中存储的新的数据行，数据类型是record。

OLD: 该变量为delete/update操作触发的行级触发器中存储的旧的数据行，数据类型是record。

TG_NAME: 数据类型是name，包含实际触发的触发器名。

TG_WHEN: 内容为 "BEFORE"或"AFTER"的字符串用于指定是BEFORE触发器还是AFTER触发器

TG_LEVEL: 内容为 "ROW"或"STATEMENT"的字符串用于指定是语句触发器还是行级触发器

TG_OP: 内容为"INSERT","UPDATE","DELETE","TRUNCATE"之一的字符串，用于指定DML语句类型。

TG_RELLD: 触发器所在的表的OID

TG_RELNAME:

TG_TABLE_NAME: 触发器所在的表的名称。

TG_TABLE_SCHEMA: 触发器所在的表的模式

TG_NARGS: 在create trigger语句里赋予触发器过程的参数个数。

TG_ARGV[]:为 text类型的一个数组，是create trigger语句里的参数。

表空间：

表空间实际上是为表指定一个存储的目录。

在创建数据库的时候可以为数据库指定默认表空间。

创建表和索引时可以指定表空间，这样表和索引就可以存储到表空间对应的目录下。

 

创建表空间示例：

CREATE TABLESPACE tbs_data location '/data/pgdata';

创建数据库时指定默认表空间：

create database db01 tablespace tbs_data;

改变一个数据库的默认表空间：

alter database  db01 set TABLESPACE tbs_data;

注意：

1.该操作时，必须没有人连接到这个库，否则报错 is being accessed by other users.detail:there is 1 other session using the database.

2.改变默认表空间时，数据库中已有表的表空间不会改变。

 

创建表时指定表空间：

create table test01(id int, note text) tablespace tbs_data;

创建索引时指定表空间：

create index idx_test01_id on test01(id) tablespace tbs_data;

增加主键时，指定主键索引的表空间，如下：

alter table test01 add constraint pk_test01_id primary key (id) using index tablespace tbs_data;

把表从一个表空间移动到另一个表空间：(会锁表)

alter table test01 set tablespace pg_default;

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

视图：

查询语句定义的虚拟表

1.可使复杂的查询易于理解和使用；

2.安全，隐藏敏感神经；

3.把函数的返回结果映射成视图；

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

索引

索引的好处与代价

索引的分类：

 

B-tree：适合处理等值查询和范围查询；

Hash：只能处理简单的等值查询；

GiST：不是一种单独的索引类型，而是一种架构，可在这种架构上实现很多不同的索引策略。

SP-GiST:即空间分区GiST索引，提高GiST索引在某些情况下特性。

GIN：反转索引。可以处理包含多个键的值，如数组等。

 

创建索引

一般在建索引过程中，会把表的数据读一遍，这个过程所用的时间由表的大小决定，对于比较大的表，可能会花很久的时间。

建索引过程中，对表的查询可正常进行，对表的增删改需要等索引建完后才能进行；

 

create [UNIQUE] index [CONCURRENTLY] on table_name [USING method]

({column_name | (expression)} [COLLATE collation] [opclass] [ASC | DESC] [NULLS {FIRST | LAST})] )

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

 

示例：

create table contacts(

id int primary key,

name varchar(40),

phone varchar(32)[],

address text

);

--name上建B-tree索引

create index idx_contacts_name on contacts(name);

--phone上建GIN索引

create index idx_contacts_phone on contacts using gin(phone);

select * from contacts where phone @> array['132323443;::varchar(32)];

注意： @> 是数组的操作符，表示“包含”的意思，GIN索引能在 “@>”上起作用。

 

创建索引时可以指定存储参数“WITH (storage_parameter=value)”,常用的参数为FILLFACTOR.如：

create index idx_contacts_name on contacts(name) with (FILLFACTOR=50);

可按降序建索引：

create index idx_contacts_name on contacts(name desc);

如果name中有空值，可在建索引时，指定空值排在非空值前面：

create index idx_contacts_name on contacts(name desc nulls first);

或空值排在非空值后面：

create index idx_contacts_name on contacts(name desc nulls last);

 

 

并发创建索引

鉴于创建索引过程中，不能对表做更新操作，当表数据量大时，耗时很长，Postgresql支持不长时间阻塞更新的情况下创建索引，这是通过在create index中加concurrently(并发创建索引)选项来实现的。当该选项被使用时，postgresql会执行表的两次扫描，因此需要更长一些的时间来建索引。

 

一个表经过频繁更新后，若要重新建索引：

 postgresql支持在同个字段上建两个索引，可使用“CONCURRENTLY”选项建一个新的索引，然后把旧的索引删掉，这样相当于重建。

 

 并发创建索引的时候，如果索引在创建的过程中被强行取消，可能会留下一个无效索引，这个索引仍会导致更新变慢。

 如果创建的是一个唯一索引，这个无效的索引还会导致插入重复值失败。此时，手工删除此索引就可以了。

 

修改索引

--给索引改名

ALTER INDEX name RENAME TO new_name;

--把索引移到表空间下

ALTER INDEX name SET TABLESPACE tablespace_name;

--把索引的填充因子（filfactor）设置为50

ALTER INDEX name SET (storage_parameter = value);

ALTER INDEX name SET (filfactor = 50);

--把填充因子重置为默认值

ALTER INDEX name RESET (storage_parameter);

ALTER INDEX name RESET (filfactor);

 

删除索引

DROP INDEX [IF EXISTS] name [cascade | restrict];

删除索引时默认使用选项“restrict”,所以不加关键字“restrict”都是一样，如果有依赖对象依赖这个索引，则会删除失败。而使用“cascade”，表示当有依赖于这个索引的对象时，一并把这些对象删掉，如外键约束。

 

用户及权限管理

在postgresql中，用户与角色是没有区别的，在整个数据库实例中都是全局的，且在同一个实例中的不同数据库中，看到的用户也都是相同的。

安装数据库的时候，预定义的超级用户与初始化该数据库的操作系统用户名相同，如果数据库建立在系统用户“postgres”下的，这个数据库超级用户就是“postgres”。用这个用户连接数据库，然后创建更多的用户。

 

创建用户和角色：

create role name [[WITH] option [...]]   --默认没有 login 权限

或

create user name [[WITH] option [...]]  --默认有 login 权限

 

option内容如下：

superuser|nosuperuser:表示创建出的用户是否为超级用户，只有超级用户才能创建超级用户。

createdb|nocreatedb:创建的用户是否有 create database权限；

createrole|nocreaterole:

createruser|nocreateruser:

inherit|noinherit: 创建的用户拥有某一个或某几个角色，若指定inherit表示用户自动继承相应角色的权限，否则没有该角色的权限。

login|nologin:是否有login权限

CONNECTION LIMIT connlimit: 用户可以使用并发连接数量，默认为-1,表示没有限制。

[ENCRYPTED|UNENCRYPTED] PASSWORD 'password':用于控制存储在系统表里的口令是否加密。

VALID UNTIL 'timestamp':密码失效时间，如果不指定，口令将永远有效；

 

权限管理：

删除一个对象及任意修改它的权力都不赋予别人，它是所有者固有的，不能被赋予和撤销。

一个用户的权限分为两类：

一类是在创建用户时就指定的权限，如：

超级用户权限；

创建数据库权限；

是否允许LOGIN的权限；

这些权限可使用alter role命令来修改。

还有一类是用grant和revoke来管理的，如下：

创建模式；

创建临时表；

连接某个数据库；

创建数据库对象，如：表，视图，函数等；

对表做select,update,insert,delete操作；

对序列进行查询、使用、更新等；

在声明表上创建触发器；

把表和索引建到指定表空间等；

 

alter role_name [[WITH] option [...]]

grant some_privileges on database_object_type object_name TO role_name;

 

postgredsql只允许超级用户使用PL语言写函数；

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

锁

锁模式

 

死锁及防范

两个及两个以上事务在执行过程中由于资源竞争造成相互等待。

防范：破坏死锁的条件；

 

表级锁命令： LOCK TABLE

LOCK TABLE name [IN lockmode MODE] [NOWAIT]

name:表名

lockmode:几种表级锁模式

nowait:如果没有nowait这个关键字，当无法获取锁时，会一直等待；加上nowait后，无法获取锁时，会立即退出并发出一个错误信息。

 

行级锁命令

select  ... for {update|share} [OF table_name [...]] [nowait]

 

锁的查看

查询系统视图pg_locks

testdb2=# \d pg_locks

 locktype           | text     |

 database           | oid      |

 relation           | oid      |

 page               | integer  |

 tuple              | smallint |

 virtualxid         | text     |

 transactionid      | xid      |

 classid            | oid      |

 objid              | oid      |

 objsubid           | smallint |

 virtualtransaction | text     |

 pid                | integer  |

 mode               | text     |

 granted            | boolean  |

 fastpath           | boolean  |

 

select

locktype,

relation::regclass as rel,

virtualxid as vxid,

transactionid as xid,

virtualtransaction as vxid2,

pid,

mode,

granted

from pg_locks

where pid = 12373;

 

   locktype  | rel  | vxid  | xid | vxid2 |  pid  |        mode         | granted

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

 virtualxid |      | 3/378 |     | 3/378 | 12373 | ExclusiveLock       | t

 relation   | test |       |     | 3/378 | 12373 | AccessExclusiveLock | t

(2 rows)

 

 

select

locktype,

relation::regclass as rel,

virtualxid as vxid,

transactionid as xid,

virtualtransaction as vxid2,

pid,

mode,

granted

from pg_locks ;

 

  locktype  |   rel    | vxid  | xid | vxid2 |  pid  |        mode         | granted

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

 relation   | pg_locks |       |     | 5/118 | 14468 | AccessShareLock     | t

 virtualxid |          | 5/118 |     | 5/118 | 14468 | ExclusiveLock       | t

 virtualxid |          | 3/378 |     | 3/378 | 12373 | ExclusiveLock       | t

 relation   | test     |       |     | 3/378 | 12373 | AccessExclusiveLock | t