文章目录
-
- 连接服务器
-
- 查看帮助
- 查看连接
- 查看版本
- 配置参数
- 退出客户端
- 角色、用户和组
-
- 创建角色
- 查看角色
- 修改密码
- 设置密码失效时间
- 用户授权
- 查看权限
- 撤销权限
- 设置当前角色
- 删除角色
- 数据库和模式
-
- 查看数据库
- 创建数据库
- 修改数据库
- 删除数据库
- 查看模式
- 创建模式
- 修改模式
- 模式搜索路径
- 删除模式
- 管理数据表
-
- 创建表
- 查看所有表
- 查看表结构
- 增加字段
- 修改字段
- 重命名字段
- 删除字段
- 增加约束
- 删除约束
- 重命名表
- 删除表
- 索引
-
- 创建索引
- 查看索引
- 维护索引
- 删除索引
- 管理表空间
-
- 创建表空间
- 查看表空间
- 修改表空间
- 删除表空间
- 备份与还原
-
- 使用 pg_dump 执行逻辑备份
- 使用 psql/pg_restore 执行还原
- 备份/还原整个数据库集群
- 使用 COPY 导入/导出表数据
- 查询语句
-
- 单表查询
- 查询条件
- 排序显示
- 限定数量
- 分组操作
- 多表连接
- 子查询
- 集合运算
- 通用表表达式
- DML 语句
-
- 插入数据
- 更新数据
- 删除数据
- 合并数据
- 事务控制
-
- 开始事务
- 提交事务
- 回滚事务
- 事务保存点
- 隔离级别
- 视图
-
- 创建视图
- 查看所有视图
- 查看视图定义
- 修改视图
- 删除视图
- 存储过程/函数
-
- 创建存储过程/函数
- 调用存储过程/函数
- 重命名存储过程/函数
- 删除存储过程/函数
- 触发器
-
- 创建触发器
- 查看触发器
- 修改触发器
- 启用/禁用触发器
- 删除触发器
大家好，我是只谈技术不剪发的 Tony 老师。本文为大家精心整理了 PostgreSQL 数据库中最常用的语句和命令，点击收藏以备不时之需！如果需要 PDF 版本，可以留下邮箱。
如果你点击了收藏，也欢迎关注️、评论、点赞
连接服务器
使用 psql 客户端工具连接 PostgreSQL 服务器的命令行如下：
psql -h hostname -p port -U username -W dbname
其中，hostname 表示服务器主机名，默认为本机；port 表示 PostgreSQL 实例服务端口，默认为 5432；username 表示用户名，默认为当前操作系统用户；-W 提示输入密码，默认行为；dbname 表示要连接的数据库，默认和用户名相同。例如：
[tony@sqlhost ~]> psql -h 192.168.56.104 -p 5432 -U tony hrdb
Password for user tony:
psql (12.4)
Type "help" for help.
hrdb=>
以上命令使用 tony 用户通过 5432 端口连接到服务器 192.168.56.104 上的 hrdb 数据库。
查看帮助
在 psql 提示符中输入help获取使用帮助。
hrdb=> help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
使用\?命令可以获取 psql 命令相关的帮助信息：
hrdb=> \?
General
\copyright show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display results in crosstab
\errverbose show most recent error message at maximum verbosity
\g [FILE] or ; execute query (and send results to file or |pipe)
\gdesc describe result of query, without executing it
\gexec execute query, then execute each value in its result
\gset [PREFIX] execute query and store results in psql variables
\gx [FILE] as \g, but forces expanded output mode
\q quit psql
\watch [SEC] execute query every SEC seconds
Help
\? [commands] show help on backslash commands
\? options show help on psql command-line options
\? variables show help on special variables
\h [NAME] help on syntax of SQL commands, * for all commands
...
使用\h命令可以获取 SQL 命令相关的帮助信息：
hrdb=> \h
Available help:
ABORT CREATE FOREIGN DATA WRAPPER DROp ROUTINE
ALTER AGGREGATE CREATE FOREIGN TABLE DROP RULE
ALTER COLLATION CREATE FUNCTION DROP SCHEMA
ALTER CONVERSION CREATE GROUP DROP SEQUENCE
ALTER DATABASE CREATE INDEX DROP SERVER
ALTER DEFAULT PRIVILEGES CREATE LANGUAGE DROP STATISTICS
...
hrdb=> \h insert
Command: INSERT
Description: create new rows in a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
[ ON CONFLICT [ conflict_target ] conflict_action ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
where conflict_target can be one of:
( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERe index_predicate ]
ON CONSTRAINT constraint_name
and conflict_action is one of:
DO NOTHING
DO UPDATE SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
[ WHERe condition ]
URL: https://www.postgresql.org/docs/12/sql-insert.html
查看连接
使用 psql 中的\conninfo命令可以查看当前连接信息。
hrdb=> \conninfo
You are connected to database "hrdb" as user "tony" on host "192.168.56.104" at port "5432".
查询系统视图 pg_catalog.pg_stat_activity 可以列出所有的服务器进程。
hrdb=> SELECT datname, pid, usename, client_addr, wait_event, state, backend_type
hrdb-> FROM pg_catalog.pg_stat_activity;
datname | pid | usename | client_addr | wait_event | state | backend_type
----------+-------+----------+--------------+---------------------+--------+------------------------------
| 1734 | | | AutoVacuumMain | | autovacuum launcher
| 1736 | postgres | | LogicalLauncherMain | | logical replication launcher
postgres | 27031 | postgres | | | active | client backend
hrdb | 27898 | postgres | 192.168.56.1 | ClientRead | idle | client backend
hrdb | 27900 | postgres | 192.168.56.1 | ClientRead | idle | client backend
hrdb | 27902 | postgres | 192.168.56.1 | ClientRead | idle | client backend
| 1732 | | | BgWriterHibernate | | background writer
| 1731 | | | CheckpointerMain | | checkpointer
| 1733 | | | WalWriterMain | | walwriter
(9 rows)
psql 中的 SQL 命令以;或者\g结束。其中，client backend 是客户端的连接进程。
如果想要取消正在运行中的查询，可以执行以下语句：
SELECt pg_cancel_backend(pid);
其中，pid 是执行语句的后台进程 id，通过上文中的查询可以获得。
如果想要强制终止某个后台连接进程，可以执行以下语句：
SELECT pg_terminate_backend(pid);
其中，pid 是执行语句的后台进程 id，通过上文中的查询可以获得。
关于如何查看和终止 PostgreSQL 连接会话的详细内容，可以参考这篇文章。
查看版本
例如，以下语句可以查看 PostgreSQL 服务器的版本：
hrdb=> SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
配置参数
PostgreSQL 运行时的参数设置可以通过SHOW语句进行查看：
SHOW name
SHOW ALL
例如：
hrdb=> show shared_buffers;
shared_buffers
----------------
256MB
(1 row)
运行时的参数可以通过SET语句、修改 postgresql.conf 配置文件、设置 PGOPTIONS 环境变量（使用 libpq 或者基于 libpq应用连接）或者启动服务时的命令行参数进行设置。
使用 SET 语句设置参数的命令如下：
SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT }
如果通过配置文件修改了参数，可以使用以下语句重新加载配置，而不需要重启服务：
SELECT pg_reload_conf();
关于 PostgreSQL 服务器参数的配置和优化，可以参考这篇文章。
退出客户端
使用exit、quit或者\q命令退出 psql 客户端：
postgres=# \q
[root@sqlhost ~]#
关于 PostgreSQL 客户端工具 psql 的详细使用，可以参考这篇文章。
角色、用户和组
PostgreSQL 通过角色的概念来控制数据库的访问权限。角色又包含了两种概念，具有登录权限的角色称为用户，包含其他成员（也是角色）的角色称为组（group）。因此，一个角色可以是一个用户，也可以是一个组，或者两者都是。
PostgreSQL 角色与用户管理的详细内容可以参考这篇文章。
创建角色
使用CREATE ROLE语句创建一个角色：
CREATE ROLE role_name [ [ WITH ] option [ ... ] ]
option:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
其中，role_name 表示角色名、用户名或者组名；如果指定了 LOGIN 属性表示创建用户，PASSWORD 属性用于指定用户密码。例如：
CREATE ROLE tony LOGIN PASSWORD 'Pswd#123';
CREATE GROUP tony LOGIN PASSWORD 'Pswd#123';
CREATE USER tony PASSWORD 'Pswd#123';
以上语句创建了一个角色 tony，并且设置了登录密码。
CREATE USER 和 CREATE GROUP 都是 CREATE ROLE 的同义词，只是 CREATE USER 默认指定了 LOGIN 选项。
一个角色被创建之后，可以通过ALTER ROLE语句修改它的属性。例如，以下命令为用户 tony 设置了创建数据库的权限：
ALTER ROLE tony createdb;
ALTER USER tony createdb;
查看角色
PostgreSQL 中的角色信息存储在系统视图 pg_catalog.pg_roles 中：
SELECT rolname, rolsuper, rolcanlogin
FROM pg_catalog.pg_roles;
rolname |rolsuper|rolcanlogin|
-------------------------|--------|-----------|
pg_monitor |false |false |
pg_read_all_settings |false |false |
pg_read_all_stats |false |false |
pg_stat_scan_tables |false |false |
pg_read_server_files |false |false |
pg_write_server_files |false |false |
pg_execute_server_program|false |false |
pg_signal_backend |false |false |
postgres |true |true |
monitor_system_stats |false |false |
tony |false |true |
查询结果中大部分都是系统创建的角色。另外，也可以通过 pg_catalog.pg_user 查看用户信息，或者使用 psql 中的\du命令列出角色。
user 或者 current_user 函数可以用于查看当前的用户：
SELECt current_user;
current_user|
------------|
postgres |
修改密码
使用ALTER ROLE语句修改角色的密码：
ALTER ROLE tony PASSWORD 'Pswd123@';
ALTER USER tony PASSWORD 'Pswd123@';
另外，也可以使用 psql 中的\password [USERNAME]命令修改用户的密码。
设置密码失效时间
使用ALTER ROLE语句设置密码的失效时间：
ALTER ROLE tony VALID UNTIL '2020-12-12 00:00:00';
ALTER USER tony VALID UNTIL 'infinity';
第一个语句将 tony 的密码失效时间设置为 2020 年 12 月 12 日零点；第二个语句取消 tony 的密码失效时间，意味着永远有效。
用户授权
PostgreSQL 使用GRANT语句进行数据库对象的授权操作。以表为例，基本的授权语法如下：
GRANT privilege_list | ALL
ON [ TABLE ] table_name
TO role_name;
其中，privilege_list 权限列表可以是 SELECT、INSERT、UPDATE、DELETE、TRUNCATE 等，ALL 表示表上的所有权限。例如，以下语句将 employees、departments 和 jobs 表上的增删改查权限授予了 tony 用户：
GRANT SELECT, INSERT, UPDATE, DELETE
ON employees, departments, jobs
TO tony;
另一种授权方式是将某个组角色的成员资格授予其他用户，使得这些用户可以自动获得该角色的权限。例如：
GRANT monitor_system_stats TO tony;
ALTER GROUP monitor_system_stats ADD USER tony;
以上语句将用户 tony 添加为角色 monitor_system_stats 的成员。
查看权限
系统视图 information_schema.role_table_grants 或者 information_schema.table_privileges 包含了授予用户的表和视图权限：
SELECT table_catalog, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERe grantee = 'tony';
table_catalog|table_schema|table_name |privilege_type|
-------------|------------|-----------------|--------------|
hrdb |public |employees |INSERT |
hrdb |public |employees |SELECt |
hrdb |public |employees |UPDATE |
hrdb |public |employees |DELETE |
...
其他对象的权限可以通过 information_schema 视图 role_column_grants（column_privileges）、role_routine_grants（routine_privileges）、role_udt_grants（udt_privileges）、role_usage_grants（usage_privileges）等进行查看。
撤销权限
PostgreSQL 使用REVOKE语句撤销数据库对象上的权限。同样以表为例，基本的撤销授权语句如下：
REVOKE privilege_list | ALL
ON TABLE table_name
FROM role_name;
其中的参数与GRANT语句相同。以下语句撤销了用户 tony 对 employees、departments 和 jobs 表的增删改查权限：
REVOKE SELECt, INSERT, UPDATE, DELETE
ON employees, departments, jobs
FROM tony;
同样，可以使用 REVOKE 语句撤销某个用的成员资格。例如以下语句撤销了用户 tony 的 monitor_system_stats 成员资格：
REVOKE monitor_system_stats
FROM tony;
ALTER GROUP monitor_system_stats DROp USER tony;
设置当前角色
SET ROLE语句可以设置当前会话的用户 ID。例如：
SELECT session_user, current_user;
session_user|current_user|
------------|------------|
postgres |postgres |
SET ROLE tony;
SELECT session_user, current_user;
session_user|current_user|
------------|------------|
postgres |tony |
以上语句将当前用户设置为 tony。此时，该会话只拥有 tony 用户的权限，不再拥有超级用户权限。可以使用以下语句要恢复初始状态的会话权限：
SET ROLE NONE;
RESET ROLE;
删除角色
删除角色可以使用DROP ROLE语句。例如以下语句删除了用户 tony：
DROP ROLE IF EXISTS tony;
另外，DROP USER和DROP GROUP语句都是 DROP ROLE 的同义词。
数据库和模式
PostgreSQL 中的数据库（Database）由一组相关的对象组成，例如表、索引、视图、存储过程等。数据库中的对象使用模式（Schema）进行组织。因此，一个数据库由多个模式组成，一个模式由许多对象组成。
PostgreSQL 数据库和模式管理的详细内容可以参考这篇文章。
查看数据库
使用 psql 工具的\l命令可以查看当前集群中的数据库：
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
ds2 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
hrdb | tony | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
pagila | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(6 rows)
也可以通过 pg_catalog.pg_database 查看所有的数据库：
SELECT datname
FROM pg_catalog.pg_database;
datname |
---------|
postgres |
template1|
template0|
pagila |
ds2 |
hrdb |
创建数据库
PostgreSQL 使用CREATE DATABASE语句创建数据库：
CREATE DATABASE db_name
[ [ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ TABLESPACE [=] tablespace_name ]
[ ALLOW_CONNECTIONS [=] allowconn ]
[ CONNECTION LIMIT [=] connlimit ]
[ IS_TEMPLATE [=] istemplate ] ]
其中，db_name 是数据库的名称，OWNER 用于指定数据库的拥有者，TEMPLATE 是创建数据库时使用的模板（默认使用 template1 数据库），ENCODING 用于设置数据库的字符集编码。
例如，以下语句创建了一个名为 testdb 的数据库：
CREATE DATABASE testdb;
修改数据库
PostgreSQL 使用ALTER DATABASE语句修改数据库的属性和配置：
ALTER DATABASE db_name RENAME TO new_name
ALTER DATABASE db_name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER DATABASE db_name SET TABLESPACE new_tablespace
ALTER DATABASE db_name [ [ WITH ] option [ ... ] ]
option:
ALLOW_CONNECTIONS allowconn
CONNECTION LIMIT connlimit
IS_TEMPLATE istemplate
例如，以下语句将 testdb 的名称修改为 newdb：
ALTER DATABASE testdb RENAME TO newdb;
除了修改常见的属性之外，ALTER DATABASE语句还可以用于修改运行时配置变量的会话默认值：
ALTER DATABASE db_name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE db_name RESET configuration_parameter
例如，以下语句将会默认禁用数据库 newdb 中的索引扫描：
ALTER DATABASE newdb SET enable_indexscan TO off;
删除数据库
PostgreSQL 使用DROp DATABASE语句删除数据库，该数据库中的所有对象以及与该数据库相关的数据目录也会被删除：
DROP DATABASE [IF EXISTS] db_name;
我们将 newdb 数据库和相关的数据删除：
DROP DATABASE newdb;
如果存在任何目标为该数据库的连接，无法执行删除操作。
查看模式
创建了数据库之后，还需要创建模式才能够存储数据库对象。通常在创建一个新的数据库时，默认会创建一个公共模式 public。我们再次创建一个数据库 testdb：
CREATE DATABASE testdb;
postgres=# \c testdb;
You are now connected to database "testdb" as user "postgres".
其中，\c用于连接数据库。然后使用\dn查看当前数据库中的模式：
testdb=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
另外，也可以系统视图 pg_catalog.pg_namespace 查询所有的模式：
SELECT *
FROM pg_catalog.pg_namespace;
oid |nspname |nspowner|nspacl |
-----|------------------|--------|-----------------------------------|
99|pg_toast | 10|NULL |
12314|pg_temp_1 | 10|NULL |
12315|pg_toast_temp_1 | 10|NULL |
11|pg_catalog | 10|{postgres=UC/postgres,=U/postgres} |
2200|public | 10|{postgres=UC/postgres,=UC/postgres}|
13887|information_schema| 10|{postgres=UC/postgres,=U/postgres} |
查询结果还返回了系统提供的其他模式。
创建模式
PostgreSQL 使用CREATE SCHEMA语句创建一个新的模式：
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_name ]
其中，schema_name 是模式名，role_name 是模式的拥有者，默认为执行该语句的用户。例如：
CREATE SCHEMA app AUTHORIZATION tony;
该语句创建了一个新的模式 app，拥有者为 tony。
修改模式
如果需要修改已有模式的属性 ，可以使用ALTER SCHEMA语句：
ALTER SCHEMA schema_name RENAME TO new_name
ALTER SCHEMA schema_name OWNER TO new_owner
例如，以下语句将模式 app 重命名为 sale：
ALTER SCHEMA app RENAME TO sale;
模式搜索路径
当我们访问数据表时，完整的对象名应该是 database.schema.table。例如：
SELECt count(*) FROM hrdb.public.employees;
为了方便书写，常常可以直接使用表名进行访问，此时 PostgreSQL 按照预定义的搜索路径查找不同模式下的对象。搜索路径由不同的模式名称组成，可以使用 SHOW 语句查看：
show search_path;
search_path
-----------------
"$user", public
(1 row)
默认的搜索路径是与当前用户同名的模式，加上 public 模式。不仅仅是对象查找，其他语句也会使用搜索路径。例如 CREATE TABLE 默认会在 “$user” 模式下创建表，如果该模式不存在，则在 public 模式下创建。
可以使用 SET 命令修改搜索路径，例如：
SET search_path TO sale,public;
删除模式
使用DROp SCHEMA语句删除模式：
DROP SCHEMA [ IF EXISTS ] schema_name [ CASCADE | RESTRICT ]
如果模式中存在对象，需要使用 CASCADE 级联删除；否则，无法删除模式。例如，以下语句将会删除模式 sale：
DROP SCHEMA sale;
管理数据表
关于 PostgreSQL 表管理的详细内容可以参考这篇文章。
创建表
PostgreSQL 使用CREATE TABLE语句创建表：
CREATE TABLE [ IF NOT EXISTS ] table_name
(
column_name data_type column_constraint,
column_name data_type,
...,
table_constraint
);
其中，table_name 指定了新表的名称；括号内是字段的定义，column_name 是字段的名称，data_type 是它的类型，column_constraint 是可选的字段约束；多个字段使用逗号进行分隔；最后，table_constraint 是可选的表级约束。
以下语句用于创建部门表和员工表：
CREATE TABLE departments
( department_id INTEGER NOT NULL PRIMARY KEY
, department_name CHARACTER VARYING(30) NOT NULL
) ;
CREATE TABLE employees
( employee_id INTEGER NOT NULL
, first_name CHARACTER VARYING(20)
, last_name CHARACTER VARYING(25) NOT NULL
, email CHARACTER VARYING(25) NOT NULL
, phone_number CHARACTER VARYING(20)
, hire_date DATE NOT NULL
, salary NUMERIC(8,2)
, commission_pct NUMERIC(2,2)
, manager_id INTEGER
, department_id INTEGER
, CONSTRAINT emp_emp_id_pk
PRIMARY KEY (employee_id)
, CONSTRAINT emp_salary_min
CHECK (salary > 0)
, CONSTRAINT emp_email_uk
UNIQUE (email)
, CONSTRAINT emp_dept_fk
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
, CONSTRAINT emp_manager_fk
FOREIGN KEY (manager_id)
REFERENCES employees(employee_id)
) ;
除了自定义表的结构之外，PostgreSQL 还提供了另一个创建表的方法，就是通过一个查询的结果创建新表：
CREATE TABLE [ IF NOT EXISTS ] table_name
AS query
[ WITH [ NO ] DATA ];
例如，我们可以基于 employees 复制出一个新的表：
CREATE TABLE emp1
AS
SELECT *
FROM employees;
emp1 只会复制 employees 的字段类型和名称，以及查询的结果，不会复制任何约束和索引。
查看所有表
在 psql 中使用\d命令可以查看当前数据库中的所有表：
testdb=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | departments | table | postgres
public | emp1 | table | postgres
public | employees | table | postgres
(3 rows)
也可以通过 information_schema.tables 查看表的信息：
SELECt table_schema, table_name
FROM information_schema.tables
WHERe table_type = 'BASE TABLE';
查看表结构
PostgreSQL 可以使用 psql 工具的\d命令查看表结构：
testdb=# \d employees
Table "public.employees"
Column | Type | Collation | Nullable | Default
----------------+-----------------------+-----------+----------+---------
employee_id | integer | | not null |
first_name | character varying(20) | | |
last_name | character varying(25) | | not null |
email | character varying(25) | | not null |
phone_number | character varying(20) | | |
hire_date | date | | not null |
salary | numeric(8,2) | | |
commission_pct | numeric(2,2) | | |
manager_id | integer | | |
department_id | integer | | |
Indexes:
"emp_emp_id_pk" PRIMARY KEY, btree (employee_id)
"emp_email_uk" UNIQUE CONSTRAINT, btree (email)
Check constraints:
"emp_salary_min" CHECK (salary > 0::numeric)
Foreign-key constraints:
"emp_dept_fk" FOREIGN KEY (department_id) REFERENCES departments(department_id)
"emp_manager_fk" FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
Referenced by:
TABLE "employees" CONSTRAINT "emp_manager_fk" FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
增加字段
增加字段使用ALTER TABLE ... ADD COLUMN语句：
ALTER TABLE table_name
ADD COLUMN column_name data_type column_constraint;
添加字段与创建表时的字段选项相同，包含字段名称、字段类型以及可选的约束。以下语句为 employees 表增加一个字段：
ALTER TABLE employees ADD COLUMN description varchar(100);
修改字段
修改字段使用ALTER TABLE ... ALTER COLUMN语句。以下语句为字段 description 设置了一个默认值：
ALTER TABLE employees ALTER COLUMN description SET DEFAULT 'No description';
以下语句将字段 description 的数据类型修改为 text：
ALTER TABLE employees ALTER COLUMN description TYPE text;
重命名字段
使用ALTER TABLE ... RENAME COLUMN语句为字段指定一个新的名称，以下语句将 description 字段重命名为 notes：
ALTER TABLE employees
RENAME COLUMN description TO notes;
删除字段
删除字段使用ALTER TABLE ... DROp COLUMN语句，以下语句用于删除 notes 字段：
ALTER TABLE employees DROP COLUMN IF EXISTS notes;
增加约束
为表增加约束可以使用ALTER TABLE语句：
ALTER TABLE table_name ADD table_constraint;
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT expression;
ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;
删除约束
删除约束同样可以使用ALTER TABLE语句：
ALTER TABLE table_name DROP CONSTRAINT IF EXISTS constraint_name [ RESTRICT | CASCADE ];
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;
ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;
重命名表
重命名表可以使用ALTER TABLE ... DROP COLUMN语句：
ALTER TABLE [ IF EXISTS ] table_name
RENAME TO new_name;
删除表
删除表可以使用DROP TABLE语句：
DROP TABLE [ IF EXISTS ] table_name [, ...] [ CASCADE | RESTRICT ]
如果存在依赖于 table_name 的视图或外键约束，需要指定 CASCADE 选项，执行级联删除。
索引
索引（Index）可以用于提高数据库的查询性能；但是索引也需要进行读写，同时还会占用更多的存储空间；因此了解并适当利用索引对于数据库的优化至关重要。
关于 PostgreSQL 索引和优化的详细内容可以参考这篇文章。
创建索引
PostgreSQL 为主键和唯一约束自动创建相应的索引，另外我们也可以手动创建索引。创建索引的命令如下：
CREATE INDEX index_name ON table_name
[USING method]
(column_name [ASC | DESC] [NULLS FIRST | NULLS LAST], ...);
其中 index_name 是索引的名称，table_name 是表的名称；method 表示索引的类型，例如 btree、hash、gist、spgist、gin 或者 brin。默认为 btree；column_name 是字段名，ASC 表示升序排序（默认值），DESC 表示降序索引；NULLS FIRST 和 NULLS LAST 表示索引中空值的排列顺序，升序索引时默认为 NULLS LAST，降序索引时默认为 NULLS FIRST。
查看索引
PostgreSQL 提供了一个关于索引的视图 pg_catalog.pg_indexes，可以用于查看索引的信息：
SELECT * FROM pg_catalog.pg_indexes;
psql 工具的\di命令也可以用于查看数据库中的索引列表。
维护索引
PostgreSQL 提供了一些修改和重建索引的方法：
ALTER INDEX index_name RENAME TO new_name;
ALTER INDEX index_name SET TABLESPACE tablespace_name;
REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } index_name;
其中，两个 ALTER INDEX 语句分别用于重命名索引和移动索引到其他表空间；REINDEX 语句用于重建索引数据，支持不同级别的索引重建。
删除索引
删除索引使用以下命令：
DROp INDEX IF EXISTS index_name [ CASCADE | RESTRICT ];
CASCADE 表示级联删除其他依赖该索引的对象；RESTRICT（默认值）表示如果存在依赖于该索引的对象，将会拒绝删除操作。
管理表空间
在 PostgreSQL 中，表空间（tablespace）表示数据文件的存放目录，这些数据文件代表了数据库的对象，例如表或索引。当我们访问表时，系统通过它所在的表空间定位到对应数据文件所在的位置。
PostgreSQL 表空间管理的详细内容可以参考这篇文章。
创建表空间
创建新的表空间使用CREATE TABLESPACE语句：
CREATE TABLESPACE tablespace_name
OWNER user_name
LOCATION 'directory';
表空间的名称不能以 ‘pg_’ 开头，它们是系统表空间的保留名称；LOCATION 参数必须指定绝对路径名，指定的目录必须是一个已经存在的空目录，PostgreSQL 操作系统用户（postgres）必须是该目录的拥有者，以便能够进行文件的读写。
PostgreSQL 支持在CREATE DATABASE、CREATE TABLE、CREATE INDEX 以及 ADD CONSTRAINT 语句中指定 tablespace_name 选项，覆盖默认的表空间（pg_default）；也可以使用相应的 ALTER … 语句将对象从一个表空间移到另一个表空间。
查看表空间
系统视图 pg_catalog.pg_tablespace 中包含了所有表空间的信息：
SELECT * FROM pg_catalog.pg_tablespace;
另外，psql 工具的\db命令也可以列出所有表空间。
修改表空间
如果需要修改表空间的定义，可以使用ALTER TABLESPACE语句：
ALTER TABLESPACE tablespace_name RENAME TO new_name;
ALTER TABLESPACE tablespace_name OWNER TO new_owner;
ALTER TABLESPACE tablespace_name SET ( tablespace_option = value [, ... ] );
ALTER TABLESPACE tablespace_name RESET ( tablespace_option [, ... ] );
第一个语句用于表空间的重命名；第二个语句用于修改表空间的拥有者；最后两个语句用于设置表空间的参数。PostgreSQL 支持设置的表空间参数包括 seq_page_cost、random_page_cost 以及 effective_io_concurrency，它们用于查询计划器选择执行计划时的代价评估。
删除表空间
对于不再需要的表空间，可以使用DROp TABLESPACE语句进行删除：
DROP TABLESPACE [ IF EXISTS ] tablespace_name;
IF EXISTS可以避免删除不存在的表空间时产生错误信息。删除表空间时，同时会删除文件系统中对应的表空间子目录。
备份与还原
关于 PostgreSQL 备份与恢复的详细介绍可以参考这篇文章。
使用 pg_dump 执行逻辑备份
pg_dump 是 PostgreSQL 逻辑备份工具，用于导出创建数据库（CREATE DATABASE）和插入数据的文本文件或者其他格式文件。
使用 pg_dump 文本格式备份指定数据库的命令如下：
pg_dump db_name > file_name.sql
其中，db_name 表示数据库名；file_name 表示备份文件名。
pg_dump 也可以选择导出指定的表：
pg_dump -t 'table_name*' db_name > file_name.sql
以上命令表示导出数据库 db_name 中名字以 table_name 开头的表。
使用其他格式备份数据库的语法如下：
pg_dump -Fc db_name -f file_name.dmp
pg_dump -Fd db_name -f file_dir
pg_dump -Ft db_name -f file_name.tar
使用 psql/pg_restore 执行还原
对于 sql 文件格式的备份，可以使用 psql 还原数据库：
psql newdb -f file_name.sql
pg_dump 和 psql 支持的读写管道功能使得我们可以直接将数据库从一个服务器导出到另一个服务器，例如：
pg_dump -h host1 db_name | psql -h host2 db_name
其他格式的备份需要pg_restore 工具进行还原：
pg_restore -d newdb file_name.dmp
pg_restore -d newdb file_dir
pg_restore -d newdb file_name.tar
备份/还原整个数据库集群
PostgreSQL 提供了导出数据库集群的 pg_dumpall 工具。
pg_dumpall -f cluster.sql
pg_dumpall 导出 sql 文件格式的备份，还原时直接使用 psql 导入相关文件即可。
psql -f cluster.sql postgres
使用 COPY 导入/导出表数据
使用COPY命令可以导出单个表中的数据或查询结果集：
COPY table_name
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
[ WHERe condition ]
COPY { table_name | ( query ) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
查询语句
单表查询
PostgreSQL 简单查询可以参考这篇文章。
查询单个表中的字段：
SELECt column1, column2, ...
FROM table_name;
查询所有字段：
SELECt * FROM table_name;
排除查询结果中的重复数据：
SELECt DISTINCT column1, column2, ...
FROM table_name;
查询条件
关于 PostgreSQL 查询条件的详细介绍可以参考这篇文章。
使用WHERe指定查询条件：
SELECt column1, column2, ...
FROM table
WHERe conditions;
常用的查询条件包括：=、!=、<>、<、<=、>、>=、BETWEEN、IN、EXISTS、LIKE、AND、OR、NOT、IS [NOT] NULL 等。
模糊匹配
使用LIKE运算符进行简单的字符串模式匹配：
expr LIKE pattern [ESCAPE escape_character]
其中，pattern 用于指定一个匹配模式，百分号（%）匹配任意多个字符，下划线（_）匹配任意单个字符；escape_character 指定转义字符。例如：
SELECt first_name,
last_name
FROM employees
WHERe last_name LIKE 'Kin%';
first_name | last_name
------------+-----------
Steven | King
Janette | King
(2 rows)
另外，NOT LIKE运算符匹配与 LIKE 相反的结果。PostgreSQL 同时还提供了不区分大小写的[NOT] ILIKE运算符。
复杂条件
WHERe 子句可以包含多个条件，使用逻辑运算符（AND、OR、NOT）将它们进行组合，并根据最终的逻辑值进行过滤。对于AND运算符，只有当它两边的结果都为真时，最终结果才为真；否则最终结果为假，不返回结果。OR逻辑或运算符只要有一个条件为真，结果就为真。当我们组合 AND 和 OR 运算符时，AND 运算符优先级更高。
排序显示
关于 PostgreSQL 的排序操作可以参考这篇文章。
指定排序字段的方式如下：
SELECt column1, column2, ...
FROM table_name
ORDER BY column1 ASC, column2 DESC, ...;
PostgreSQL 支持使用 NULLS FIRST（空值排在最前）和 NULLS LAST（空值排在最后）指定空值的排序位置；升序排序时默认为 NULLS LAST，降序排序时默认为 NULLS FIRST。
限定数量
关于 PostgreSQL Top-N 和分页查询的实现可以参考这篇文章。
PostgreSQL 支持 SQL 标准的 FETCH 和 OFFSET 子句，以及扩展的 LIMIT 语法限制返回结果的数量：
SELECt column1, column2, ...
FROM table_name
[WHERe conditions]
[ORDER BY column1 ASC, column2 DESC, ...]
[OFFSET m {
ROW | ROWS}]
[FETCH { FIRST | NEXT } [ num_rows] { ROW | ROWS } ONLY];
SELECt column1, column2, ...
FROM table_name
[WHERe conditions]
[ORDER BY column1 ASC, column2 DESC, ...]
[LIMIT { num_rows| ALL } ]
[OFFSET m {
ROW | ROWS}];
分组操作
关于 PostgreSQL 分组汇总的具体介绍可以参考这篇文章。
指定分组和过滤：
SELECt column1, column2, agg_func()
FROM table_name
GROUP BY column1, column2
HAVINg conditions;
常用的聚合函数：AVG、COUNT、MIN、MAX、SUM、STRING_AGG 等。PostgreSQL 除了支持基本的分组操作之外，还支持 3 种高级的分组选项：GROUPING SETS、ROLLUP 以及 CUBE。
多表连接
关于 PostgreSQL 连接查询这篇文章。
连接查询用于从多个表中查询关联数据：
SELECt t1.column1, t2.column2, ...
FROM table1 AS t1
[INNER | LEFT | RIGHT | FULL | CROSS] JOIN table2 AS t2
ON conditions;
子查询
关于 PostgreSQL 子查询的介绍可以参考这篇文章。
FROM 子句中的子查询被称为派生表：
SELECt column1, column2, ...
FROM (subquery) AS table_alias;
WHERe 条件中的子查询可以与 IN、ALL、ANY 等操作符一起使用。通过增加LATERAL关键字，横向子查询可以引用左侧表中的列：
SELECt table1.colum1, t2.col1, ...
FROM table1
JOIN LATERAL (
SELECt ...
FROM table2
WHERe table1.col1 = table1.column1) t2;
EXISTS 操作符与关联子查询：
SELECt table1.column1, table1.column12, ...
FROM table1
WHERe EXISTS ( SELECt 1
FROM table2
WHERe table2.col1 = table1.col1);
集合运算
关于 PostgreSQL 集合运算符的使用可以参考这篇文章。
集合运算包括并集、交集和差集：
SELECt column1, column2
FROM table1
UNIOn [DISTINCT | ALL]
SELECt col1, col2
FROM table2;
SELECt column1, column2
FROM table1
INTERSECT [DISTINCT | ALL]
SELECt col1, col2
FROM table2;
SELECt column1, column2
FROM table1
EXCEPT [DISTINCT | ALL]
SELECt col1, col2
FROM table2;
其中 ALL 表示保留结果集中的重复记录；DISTINCT（默认值）表示去除查询结果中的重复记录。相同的集合操作符按照从左至右的顺序执行，INTERSECT 的优先级高于 UNIOn 和 EXCEPT，使用括号可以修改集合操作的执行顺序。
通用表表达式
关于PostgreSQL 通用表表达式的详细内容可以参考这篇文章。
通用表表达式类似于派生表或者语句级别的视图，但是可读性和性能更好：
WITH cte_name (col1, col2, ...) AS (
cte_query_definition
)
sql_statement;
递归 CTE 允许在它的定义中进行自引用，以下查询生成 1 到 10 的数字序列：
WITH RECURSIVE cte(n) AS (
SELECt 1 AS n -- 初始查询
UNIOn ALL
SELECT n+1 FROM cte WHERe n < 10) -- 递归查询
SELECt * FROM cte;
DML 语句
关于 PostgreSQL 数据修改语句的使用可以参考这篇文章。
插入数据
插入数据使用INSERT语句：
INSERT INTO table_name(column1, column2, ...)
VALUES (value1, value2, ...);
一次插入多条记录：
INSERT INTO table_name(column1, column2, ...)
VALUES (val11,val12,...), (val21,val22,...), (val31,val32,...);
插入查询语句的结果：
INSERT INTO table_name(column1, column2, ...)
SELECt ...;
PostgreSQL 对 SQL 进行了扩展，可以在 INSERT 语句之后使用 RETURNING 返回插入的数据值：
INSERT INTO table_name(column1, column2, ...)
...
RETUNRING ...;
更新数据
PostgreSQL 使用UPDATE语句更新表中已有的数据：
UPDATE table_name
SET column1 = value1,
column2 = value2,
...
WHERe conditions;
其中，WHERe 决定了需要更新的数据行，只有满足条件的数据才会更新；如果省略 WHERe 条件，将会更新表中的所有数据。
另外，PostgreSQL 还支持通过关联其他表中的数据进行更新：
UPDATE table1
SET column1 = table2.salary,
column2 = value2,
...
FROM table2
WHERe conditions;
PostgreSQL 同样对 UPDATe 语句进行了扩展，支持使用 RETURNING 返回更新后的数据值：
UPDATE table_name
SET ...
WHERe conditions
RETURNING ...;
删除数据
删除数据可以使用DELETE语句：
DELETE FROM table_name
WHERe conditions;
同样，只有满足WHERe条件的数据才会被删除；如果省略，将会删除表中所有的数据。
PostgreSQL 同样支持通过关联其他表进行数据删除：
DELETe
FROM table1
USING table2
WHERe conditions;
DELETe 语句也可以使用 RETURNING 返回被删除的数据：
DELETE FROM table_name
WHERe conditions
RETURNING ...;
另外，TRUNCATE TABLE语句用于快速清除表中的全部数据：
TRUNCATE TABLE table_name;
合并数据
PostgreSQL 可以通过INSERT INTO ON CONFLICT语句实现数据合并（MERGE）的功能：
INSERT INTO table_name(column1, column2, ...)
...
ON CONFLICT [conflict_target]
{
DO NOTHING | DO UPDATe SET ... [WHERe contidions]};
其中，conflict_target 是判断数据是否存在冲突的条件：
- ( { index_column_name | ( index_expression ) } )，基于某个具有索引的字段或者表达式进行判断；
- ON CONSTRAINT constraint_name，基于某个唯一约束进行判断。
事务控制
关于 PostgreSQL 数据库事务和事务控制语句可以参考这篇文章。
开始事务
默认情况下，PostgreSQL 执行任何语句都会自动开始一个事务并提交该事务。如果有需要，可以使用BEGIN语句手动开始一个事务：
BEGIN;
另外，也可以使用BEGIN WORK或者BEGIN TRANSACTION开始事务。
提交事务
PostgreSQL 使用COMMIT语句提交已经打开的事务：
COMMIT;
另外，也可以使用COMMIT WORK或者COMMIT TRANSACTION提交事务。
回滚事务
ROLLBACK用于回滚当前事务：
ROLLBACK;
另外，也可以使用ROLLBACK WORK或者ROLLBACK TRANSACTION回滚事务。
事务保存点
事务保存点可以用于回滚部分事务，SAVEPOINT语句用于在事务中定义保存点：
BEGIN;
...
SAVEPOINT savepoint_name;
...
ROLLBACK TO SAVEPOINT savepoint_name;
...
COMMIT;
其中，ROLLBACK TO 用于回退到保存点时的状态。另外，RELEASE SAVEPOINT savepoint_name可以释放保存点。
隔离级别
使用 SHOW 命令可以查看当前的隔离级别：
SHOW transaction_isolation;
如果需要修改当前事务的隔离级别，可以在事务的最开始执行SET TRANSACTION命令：
BEGIN;
SET TRANSACTION ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED };
...
视图
关于 PostgreSQL 视图的概念和使用，可以参考这篇文章。
创建视图
PostgreSQL 使用CREATE VIEW语句创建视图：
CREATE [OR REPLACE] VIEW view_name
AS
select-statement
WITH CHECK OPTION;
WITH CHECK OPTION 选项可以阻止通过视图修改或者插入视图范围之外的基础表数据。
查看所有视图
PostgreSQL 系统表 information_schema.views 中存储了关于视图的所有信息：
SELECT *
FROM information_schema.views;
psql 命令\dv也可以列出当前数据库中的所有视图。
查看视图定义
查看视图的定义：
SELECt view_definition
FROM information_schema.views
WHERe table_schema = 'public'
AND table_name = 'employees_it';
SELECt employees.employee_id,
employees.first_name,
employees.last_name,
employees.email,
employees.phone_number,
employees.hire_date,
employees.job_id,
employees.manager_id,
employees.department_id
FROM employees
WHERe (employees.department_id = 60);
修改视图
PostgreSQL 使用ALTER VIEW语句修改视图的信息。例如重命名视图：
ALTER VIEW IF EXISTS view_name RENAME TO new_name
删除视图
PostgreSQL 使用DROp VIEW语句删除视图：
DROP VIEW [IF EXISTS] view_name;
存储过程/函数
关于 PostgreSQL 存储过程/函数的定义和使用，可以参考这篇文章。
创建存储过程/函数
使用CREATE PROCEDURE语句创建PL/pgSQL 存储过程：
CREATE [ OR REPLACE ] PROCEDURE
procedure_name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
AS $$
DECLARE
declarations
BEGIN
statements;
...
END; $$
LANGUAGE plpgsql;
使用CREATE FUNCTION语句创建PL/pgSQL 函数：
CREATE [ OR REPLACE ] FUNCTION
function_name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
RETURNS rettype
AS $$
DECLARE
declarations
BEGIN
statements;
...
END; $$
LANGUAGE plpgsql;
调用存储过程/函数
调用存储过程使用CALL语句：
CALL procedure_name( argument1, ... );
存储函数可以像可以像内置函数一样在 SQL 语句中进行调用：
SELECT function_name( argument1, ... );
重命名存储过程/函数
使用ALTER PROCEDURE和ALTER FUNCTION语句修改存储过程/函数的属性，例如修改名称：
ALTER PROCEDURE procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
RENAME TO new_name;
ALTER FUNCTION function_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
RENAME TO new_name;
删除存储过程/函数
删除存储过程/函数使用DROP语句：
DROP PROCEDURE IF EXISTS procedure_name [ CASCADE | RESTRICT ];
DROP FUNCTION IF EXISTS function_name [ CASCADE | RESTRICT ];
触发器
关于 PostgreSQL 触发器的详细介绍，可以参考这篇文章。
创建触发器
PostgreSQL 触发器的创建分为两步：首先，使用CREATE FUNCTION语句创建一个触发器函数；然后，使用CREATE TRIGGER语句将该函数与表进行关联。
CREATE [ OR REPLACE ] FUNCTION trigger_function ()
RETURNS trigger
AS $$
DECLARE
declarations
BEGIN
statements;
...
END; $$
LANGUAGE plpgsql;
CREATE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {event [OR ...]}
ON table_name
[FOR [EACH] {
ROW | STATEMENT}]
[WHEN ( condition ) ]
EXECUTE FUNCTION trigger_function;
其中，event 可以是 INSERT、UPDATE、DELETE 或者 TRUNCATE，UPDATE 支持特定字段（UPDATE OF col1, clo2）的更新操作；触发器可以在事件之前（BEFORE）或者之后（AFTER）触发，INSTEAD OF 只能用于替代视图上的 INSERT、UPDATE 或者 DELETE 操作；FOR EACH ROW 表示行级触发器，FOR EACH STATEMENT 表示语句级触发器；WHEN 用于指定一个额外的触发条件，满足条件才会真正支持触发器函数。
查看触发器
视图 information_schema.triggers 中存储了关于触发器的信息：
SELECT *
FROM information_schema.triggers;
修改触发器
PostgreSQL 使用ALTER TRIGGER语句修改触发器，目前只支持修改触发器的名称：
ALTER TRIGGER trigger_name ON table_name RENAME TO new_name;
启用/禁用触发器
PostgreSQL 支持触发器的禁用和启用：
ALTER TABLE table_name
{
ENABLE | DISABLE} TRIGGER {trigger_name | ALL | USER};
删除触发器
删除触发器的语句如下：
DROP TRIGGER [IF EXISTS] trigger_name
ON table_name [RESTRICT | CASCADE];
