数据库：
创建数据库：
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 ;
如果还有人连接该库，将不能删除；
常见问题：
答：不能
答：能
-------------------------------------------------------------------------
模式
模式（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,id3 int,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,
price numeric
);
（3）唯一约束
create table books(
book_no integer unique,
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,
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表是看不到这条数据的；
所有父表的检查约束和非空约束会自动被子表继承，其它类型的约束不会
一个子表可以从多个父表继承，它将拥有所有父表字段的总和，并且子表中的字段也会加入。
如果一个字段出现在多个父表，或出现在父表和子表中，这些字段将进行融合，只保留一个。
------------------------------------------------------------------------------------
分区表
表分区就是把逻辑上的一个大表分割成物理上的几个小块。分区可提供若干好处：
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,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,'杭州钱塘江区小 碗城');
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,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,
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,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,(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.把函数的返回结果映射成视图；
-------------------------------------------------------------
索引
索引的好处与代价
索引的分类：
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,insert,delete操作；
对序列进行查询、使用、更新等；
在声明表上创建触发器；
把表和索引建到指定表空间等；
alter role_name [[WITH] option [...]]
grant some_privileges on database_object_type object_name TO role_name;
----------------------------------------------------------------------------------------
锁
锁模式
死锁及防范
两个及两个以上事务在执行过程中由于资源竞争造成相互等待。
防范：破坏死锁的条件；
表级锁命令： 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,
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
原文链接：https://www.f2er.com/postgresql/193502.html