PostgreSQL高级(1)
PostgreSQL 约束
PostgreSQL 约束用于规定表中的数据规则。
如果存在违反约束的数据行为，行为会被约束终止。
约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。
约束确保了数据库中数据的准确性和可靠性。
约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。
以下是在 PostgreSQL 中常用的约束。
- NOT NULL：指示某列不能存储 NULL 值。
- UNIQUE：确保某列的值都是唯一的。
- PRIMARY Key：NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。。
- FOREIGN Key： 保证一个表中的数据匹配另一个表中的值的参照完整性。
- CHECK： 保证列中的值符合指定的条件。
- EXCLUSION ：排他约束，保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回 false 或空值。
NOT NULL 约束
默认情况下，列可以保存为 NULL 值。如果您不想某列有 NULL 值，那么需要在该列上定义此约束，指定在该列上不允许 NULL 值。
NULL 与没有数据是不一样的，它代表着未知的数据。
实例
下面实例创建了一张新表叫 COMPANY1，添加了 5 个字段，其中三个 ID，NAME，AGE 设置不接受空置：
CREATE TABLE COMPANY1(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
UNIQUE 约束
UNIQUE 约束可以设置列是唯一的，避免同一列出现重复值。
实例
下面实例创建了一张新表叫 COMPANY3，添加了 5 个字段，其中 AGE 设置为 UNIQUE，因此你不能添加两条有相同年龄的记录：
CREATE TABLE COMPANY3(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL UNIQUE,
ADDRESS CHAR(50),
SALARY REAL DEFAULT 50000.00
);
PRIMARY KEY
在设计数据库时，PRIMARY KEY 非常重要。
PRIMARY KEY 称为主键，是数据表中每一条记录的唯一标识。
设置 UNIQUE 的列可能有多个，但是一张表只有一列可以设置 PRIMARY KEY。
我们可以使用主键来引用表中的行，也可以通过把主键设置为其他表的外键，来创建表之间的关系。
主键是非空约束和唯一约束的组合。
一个表只能有一个主键，它可以由一个或多个字段组成，当多个字段作为主键，它们被称为复合键。
如果一个表在任何字段上定义了一个主键，那么在这些字段上不能有两个记录具有相同的值。
实例
下面我们创建 COMAPNY4 表，其中 ID 作为主键：
CREATE TABLE COMPANY4(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
FOREIGN KEY 约束
FOREIGN KEY 即外键约束，指定列(或一组列)中的值必须匹配另一个表的某一行中出现的值。
通常一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)，即维护了两个相关表之间的引用完整性。
实例
下面实例创建了一张 COMPANY6 表，并添加了5个字段：
CREATE TABLE COMPANY6(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
下面实例创建一张 DEPARTMENT1 表，并添加 3 个字段，EMP_ID 就是外键，参照 COMPANY6 的 ID：
CREATE TABLE DEPARTMENT1(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT references COMPANY6(ID)
);
CHECK 约束
CHECK 约束保证列中的所有值满足某一条件，即对输入一条记录要进行检查。如果条件值为 false，则记录违反了约束，且不能输入到表。
实例
例如，下面实例建一个新的表 COMPANY5，增加了五列。在这里，我们为 SALARY 列添加 CHECK，所以工资不能为零：
CREATE TABLE COMPANY5(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL CHECK(SALARY > 0)
);
EXCLUSION 约束
EXCLUSION 约束确保如果使用指定的运算符在指定列或表达式上比较任意两行，至少其中一个运算符比较将返回 false 或 null。
实例
下面实例创建了一张 COMPANY7 表，添加 5 个字段，并且使用了 EXCLUDE 约束。
CREATE TABLE COMPANY7(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT,
AGE INT ,
ADDRESS CHAR(50),
SALARY REAL,
EXCLUDE USING gist
(NAME WITH =, -- 如果满足 NAME 相同，AGE 不相同则不允许插入，否则允许插入
AGE WITH <>) -- 其比较的结果是如果整个表边式返回 true，则不允许插入，否则允许
);
这里，USING gist 是用于构建和执行的索引一种类型。
您需要为每个数据库执行一次 CREATE EXTENSION btree_gist 命令，这将安装 btree_gist 扩展，它定义了对纯标量数据类型的 EXCLUDE 约束。
由于我们已经强制执行了年龄必须相同，让我们通过向表插入记录来查看这一点：
INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );
-- 此条数据的 NAME 与第一条相同，且 AGE 与第一条也相同，故满足插入条件
INSERT INTO COMPANY7 VALUES(3, 'Allen', 42, 'California', 20000.00 );
-- 此数据与上面数据的 NAME 相同，但 AGE 不相同，故不允许插入
前面两条顺利添加的 COMPANY7 表中，但是第三条则会报错：
ERROR: conflicting key value violates exclusion constraint "company7_name_age_excl"
DETAIL: Key (name, age)=(Paul, 42) conflicts with existing key (name, age)=(Paul, 32).
删除约束
删除约束必须知道约束名称，已经知道名称来删除约束很简单，如果不知道名称，则需要找到系统生成的名称，使用 \d 表名 可以找到这些信息。
通用语法如下：
ALTER TABLE table_name DROP CONSTRAINT some_name;
PostgreSQL 连接(JOIN)
PostgreSQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。
在 PostgreSQL 中，JOIN 有五种连接类型：
- CROSS JOIN ：交叉连接
- INNER JOIN：内连接
- LEFT OUTER JOIN：左外连接
- RIGHT OUTER JOIN：右外连接
- FULL OUTER JOIN：全外连接
接下来让我们创建两张表 COMPANY 和 DEPARTMENT。
实例
创建 COMPANY 表（下载 COMPANY SQL 文件 ），数据内容如下：
runoobdb# select * from COMPANY;
id | name | age | address | salary
----+-------+-----+-----------+--------
1 | Paul | 32 | California| 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall| 45000
7 | James | 24 | Houston | 10000
(7 rows)
我们往表里添加几条数据：
INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);
此时，COMPANY 表的记录如下：
id | name | age | address | salary
----+-------+-----+--------------+--------
1 | Paul | 32 | California | 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
8 | Paul | 24 | Houston | 20000
9 | James | 44 | Norway | 5000
10 | James | 45 | Texas | 5000
(10 rows)
创建一张 DEPARTMENT 表，添加三个字段：
CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);
向 DEPARTMENT 表插入三条记录：
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 );
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 );
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );
此时，DEPARTMENT 表的记录如下：
id | dept | emp_id
----+-------------+--------
1 | IT Billing | 1
2 | Engineering | 2
3 | Finance | 7
交叉连接
交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行。
由于交叉连接（CROSS JOIN）有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们。
下面是 CROSS JOIN 的基础语法：
SELECT ... FROM table1 CROSS JOIN table2 ...
基于上面的表，我们可以写一个交叉连接（CROSS JOIN），如下所示：
runoobdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
得到结果如下：
runoobdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
emp_id | name | dept
--------+-------+--------------------
1 | Paul | IT Billing
1 | Allen | IT Billing
1 | Teddy | IT Billing
1 | Mark | IT Billing
1 | David | IT Billing
1 | Kim | IT Billing
1 | James | IT Billing
1 | Paul | IT Billing
1 | James | IT Billing
1 | James | IT Billing
2 | Paul | Engineering
2 | Allen | Engineering
2 | Teddy | Engineering
2 | Mark | Engineering
2 | David | Engineering
2 | Kim | Engineering
2 | James | Engineering
2 | Paul | Engineering
2 | James | Engineering
2 | James | Engineering
7 | Paul | Finance
内连接
内连接（INNER JOIN）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。
当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。
内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。
INNER 关键字是可选的。
下面是内连接（INNER JOIN）的语法：
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;
基于上面的表，我们可以写一个内连接，如下所示：
runoobdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+--------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
(3 rows)
左外连接
外部连接是内部连接的扩展。SQL 标准定义了三种类型的外部连接: LEFT、RIGHT 和 FULL, PostgreSQL 支持所有这些。
对于左外连接，首先执行一个内连接。然后，对于表 T1 中不满足表 T2 中连接条件的每一行，其中 T2 的列中有 null 值也会添加一个连接行。因此，连接的表在 T1 中每一行至少有一行。
下面是左外连接（ LEFT OUTER JOIN ）的基础语法：
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
基于上面两张表，我们可以写个左外连接，如下：
runoobdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+----------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
| James |
| David |
| Paul |
| Kim |
| Mark |
| Teddy |
| James |
(10 rows)
右外连接
首先，执行内部连接。然后，对于表T2中不满足表T1中连接条件的每一行，其中T1列中的值为空也会添加一个连接行。这与左联接相反;对于T2中的每一行，结果表总是有一行。
下面是右外连接（ RIGHT OUT JOIN）的基本语法：
SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...
基于上面两张表，我们建立一个右外连接：
runoobdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+-----------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
(3 rows)
外连接
首先，执行内部连接。然后，对于表 T1 中不满足表 T2 中任何行连接条件的每一行，如果 T2 的列中有 null 值也会添加一个到结果中。此外，对于 T2 中不满足与 T1 中的任何行连接条件的每一行，将会添加 T1 列中包含 null 值的到结果中。
下面是外连接的基本语法：
SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...
基于上面两张表，可以建立一个外连接：
runoobdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+-----------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
| James |
| David |
| Paul |
| Kim |
| Mark |
| Teddy |
| James |
(10 rows)
PostgreSQL UNION 操作符
PostgreSQL UNION 操作符合并两个或多个 SELECT 语句的结果。
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
语法
UNIONS 基础语法如下：
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
这里的条件语句可以根据您的需要设置任何表达式。
实例
创建 COMPANY 表（下载 COMPANY SQL 文件 ），数据内容如下：
runoobdb# select * from COMPANY;
id | name | age | address | salary
----+-------+-----+-----------+--------
1 | Paul | 32 | California| 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall| 45000
7 | James | 24 | Houston | 10000
(7 rows)
创建 DEPARTMENT 表（下载 DEPARTMENT SQL 文件 ），数据内容如下：
runoobdb=# SELECT * from DEPARTMENT;
id | dept | emp_id
----+-------------+--------
1 | IT Billing | 1
2 | Engineering | 2
3 | Finance | 7
4 | Engineering | 3
5 | Finance | 4
6 | Engineering | 5
7 | Finance | 6
(7 rows)
现在，我们在 SELECT 语句中使用 UNION 子句将两张表连接起来，如下所示：
SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
得到结果如下：
emp_id | name | dept
--------+-------+--------------
5 | David | Engineering
6 | Kim | Finance
2 | Allen | Engineering
3 | Teddy | Engineering
4 | Mark | Finance
1 | Paul | IT Billing
7 | James | Finance
(7 rows)
UNION ALL 子句
UNION ALL 操作符可以连接两个有重复行的 SELECT 语句，默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
语法
UINON ALL 子句基础语法如下：
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
这里的条件语句可以根据您的需要设置任何表达式。
实例
现在，让我们把上面提到的两张表用 SELECT 语句结合 UNION ALL 子句连接起来：
SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION ALL
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
得到结果如下：
emp_id | name | dept
--------+-------+--------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
3 | Teddy | Engineering
4 | Mark | Finance
5 | David | Engineering
6 | Kim | Finance
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
3 | Teddy | Engineering
4 | Mark | Finance
5 | David | Engineering
6 | Kim | Finance
(14 rows)
PostgreSQL NULL 值
NULL 值代表遗漏的未知数据。
默认地，表的列可以存放 NULL 值。
本章讲解 IS NULL 和 IS NOT NULL 操作符。
语法
当创建表时，NULL 的基本语法如下：
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
这里，NOT NULL 表示强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
具有 NULL 值的字段表示在创建记录时可以留空。
在查询数据时，NULL 值可能会导致一些问题，因为一个未知的值去与其他任何值比较，结果永远是未知的。
另外无法比较 NULL 和 0，因为它们是不等价的。
实例
创建 COMPANY 表（下载 COMPANY SQL 文件 ），数据内容如下：
runoobdb# select * from COMPANY;
id | name | age | address | salary
----+-------+-----+-----------+--------
1 | Paul | 32 | California| 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall| 45000
7 | James | 24 | Houston | 10000
(7 rows)
接下来我们用 UPDATE 语句把几个可设置为空的字段设置为 NULL ：
runoobdb=# UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);
现在 COMPANY 表长这样：：
runoobdb=# select * from company;
id | name | age | address | salary
----+-------+-----+---------------------+--------
1 | Paul | 32 | California | 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | |
7 | James | 24 | |
(7 rows)
IS NOT NULL
现在，我们用 IS NOT NULL 操作符把所有 SALARY（薪资） 值不为空的记录列出来：
runoobdb=# SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NOT NULL;
得到结果如下：
id | name | age | address | salary
----+-------+-----+------------+--------
1 | Paul | 32 | California | 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
(5 rows)
IS NULL
IS NULL 用来查找为 NULL 值的字段。
下面是 IS NULL 操作符的用法，列出 SALARY（薪资） 值为空的记录：
runoobdb=# SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NULL;
得到结果如下：
id | name | age | address | salary
----+-------+-----+---------+--------
6 | Kim | 22 | |
7 | James | 24 | |
(2 rows)
PostgreSQL 别名
我们可以用 SQL 重命名一张表或者一个字段的名称，这个名称就叫着该表或该字段的别名。
创建别名是为了让表名或列名的可读性更强。
SQL 中 使用 AS 来创建别名。
语法
表的别名语法:
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];
列的别名语法:
SELECT column_name AS alias_name
FROM table_name
WHERE [condition];
下面我们分别用 C 和 D 表示 COMPANY 表和 DEPAERMENT 表的别名：
runoobdb=# SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID;
得到结果如下：
id | name | age | dept
----+-------+-----+------------
1 | Paul | 32 | IT Billing
2 | Allen | 25 | Engineering
7 | James | 24 | Finance
3 | Teddy | 23 | Engineering
4 | Mark | 25 | Finance
5 | David | 27 | Engineering
6 | Kim | 22 | Finance
(7 rows)
下面，我们用 COMPANY_ID 表示 ID 列，COMPANY_NAME 表示 NAME 列，来展示列别名的用法：
runoobdb=# SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID;
得到结果如下：
company_id | company_name | age | dept
------------+--------------+-----+------------
1 | Paul | 32 | IT Billing
2 | Allen | 25 | Engineering
7 | James | 24 | Finance
3 | Teddy | 23 | Engineering
4 | Mark | 25 | Finance
5 | David | 27 | Engineering
6 | Kim | 22 | Finance
(7 rows)
PostgreSQL 触发器
PostgreSQL 触发器是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。
下面是关于 PostgreSQL 触发器几个比较重要的点：
- PostgreSQL 触发器可以在下面几种情况下触发：
- 在执行操作之前（在检查约束并尝试插入、更新或删除之前）。
- 在执行操作之后（在检查约束并插入、更新或删除完成之后）。
- 更新操作（在对一个视图进行插入、更新、删除时）。
- 触发器的 FOR EACH ROW 属性是可选的，如果选中，当操作修改时每行调用一次；相反，选中 FOR EACH STATEMENT，不管修改了多少行，每个语句标记的触发器执行一次。
- WHEN 子句和触发器操作在引用 NEW.column-name 和 OLD.column-name 表单插入、删除或更新时可以访问每一行元素。其中 column-name 是与触发器关联的表中的列的名称。
- 如果存在 WHEN 子句，PostgreSQL 语句只会执行 WHEN 子句成立的那一行，如果没有 WHEN 子句，PostgreSQL 语句会在每一行执行。
- BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。
- 要修改的表必须存在于同一数据库中，作为触发器被附加的表或视图，且必须只使用 tablename，而不是 database.tablename。
- 当创建约束触发器时会指定约束选项。这与常规触发器相同，只是可以使用这种约束来调整触发器触发的时间。当约束触发器实现的约束被违反时，它将抛出异常。
语法
创建触发器时的基础语法如下：
CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
-- 触发器逻辑....
];
在这里，event_name 可以是在所提到的表 table_name 上的 INSERT、DELETE 和 UPDATE 数据库操作。您可以在表名后选择指定 FOR EACH ROW。
以下是在 UPDATE 操作上在表的一个或多个指定列上创建触发器的语法：
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
-- 触发器逻辑....
];
实例
让我们假设一个情况，我们要为被插入到新创建的 COMPANY 表（如果已经存在，则删除重新创建）中的每一个记录保持审计试验：
runoobdb=# CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
为了保持审计试验，我们将创建一个名为 AUDIT 的新表。每当 COMPANY 表中有一个新的记录项时，日志消息将被插入其中：
runoobdb=# CREATE TABLE AUDIT(
EMP_ID INT NOT NULL,
ENTRY_DATE TEXT NOT NULL
);
在这里，ID 是 AUDIT 记录的 ID，EMP_ID 是来自 COMPANY 表的 ID，DATE 将保持 COMPANY 中记录被创建时的时间戳。所以，现在让我们在 COMPANY 表上创建一个触发器，如下所示：
runoobdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();
auditlogfunc() 是 PostgreSQL 一个程序，其定义如下：
CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
RETURN NEW;
END;
$example_table$ LANGUAGE plpgsql;
现在，我们开始往 COMPANY 表中插入数据：
runoobdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );
这时，COMPANY 表中插入了一条记录：
同时， AUDIT 表中也插入了一条记录，因为我们在插入 COMPANY 表时创建了一个触发器。相似的，我们也可以根据需求在更新和删除时创建触发器：
emp_id | entry_date
--------+-------------------------------
1 | 2013-05-05 15:49:59.968+05:30
(1 row)
列出触发器
你可以把从 pg_trigger 表中把当前数据库所有触发器列举出来：
runoobdb=# SELECT * FROM pg_trigger;
如果，你想列举出特定表的触发器，语法如下：
runoobdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';
得到结果如下：
tgname
-----------------
example_trigger
(1 row)
删除触发器
删除触发器基础语法如下：
drop trigger ${trigger_name} on ${table_of_trigger_dependent};
删除本文上表 company 上的触发器 example_trigger 的指令为：
drop trigger example_trigger on company;
PostgreSQL 索引
索引是加速搜索引擎检索数据的一种特殊表查询。简单地说，索引是一个指向表中数据的指针。一个数据库中的索引与一本书的索引目录是非常相似的。
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。
使用 CREATE INDEX 语句创建索引，它允许命名索引，指定表及要索引的一列或多列，并指示索引是升序排列还是降序排列。
索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目。
CREATE INDEX 命令
CREATE INDEX （创建索引）的语法如下：
CREATE INDEX index_name ON table_name;
索引类型
单列索引
单列索引是一个只基于表的一个列上创建的索引，基本语法如下：
CREATE INDEX index_name
ON table_name (column_name);
组合索引
组合索引是基于表的多列上创建的索引，基本语法如下：
CREATE INDEX index_name
ON table_name (column1_name, column2_name);
不管是单列索引还是组合索引，该索引必须是在 WHERE 子句的过滤条件中使用非常频繁的列。
如果只有一列被使用到，就选择单列索引，如果有多列就使用组合索引。
唯一索引
使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法如下：
CREATE UNIQUE INDEX index_name
on table_name (column_name);
局部索引
局部索引 是在表的子集上构建的索引；子集由一个条件表达式上定义。索引只包含满足条件的行。基础语法如下：
CREATE INDEX index_name
on table_name (conditional_expression);
隐式索引
隐式索引 是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。
实例
下面实例将在 COMPANY 表的 SALARY 列上创建索引：
# CREATE INDEX salary_index ON COMPANY (salary);
现在，用 \d company 命令列出 COMPANY 表的所有索引：
# \d company
得到的结果如下，company_pkey 是隐式索引 ，是表创建表时创建的：
runoobdb=# \d company
Table "public.company"
Column | Type | Collation | Nullable | Default
---------+---------------+-----------+----------+---------
id | integer | | not null |
name | text | | not null |
age | integer | | not null |
address | character(50) | | |
salary | real | | |
Indexes:
"company_pkey" PRIMARY KEY, btree (id)
"salary_index" btree (salary)
你可以使用 \di 命令列出数据库中所有索引：
runoobdb=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------+-------+----------+------------
public | company_pkey | index | postgres | company
public | department_pkey | index | postgres | department
public | salary_index | index | postgres | company
(3 rows)
DROP INDEX （删除索引）
一个索引可以使用 PostgreSQL 的 DROP 命令删除。
DROP INDEX index_name;
您可以使用下面的语句来删除之前创建的索引：
# DROP INDEX salary_index;
删除后，可以看到 salary_index 已经在索引的列表中被删除：
runoobdb=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------+-------+----------+------------
public | company_pkey | index | postgres | company
public | department_pkey | index | postgres | department
(2 rows)
什么情况下要避免使用索引？
虽然索引的目的在于提高数据库的性能，但这里有几个情况需要避免使用索引。
使用索引时，需要考虑下列准则：
- 索引不应该使用在较小的表上。
- 索引不应该使用在有频繁的大批量的更新或插入操作的表上。
- 索引不应该使用在含有大量的 NULL 值的列上。
- 索引不应该使用在频繁操作的列上。
评论