個人中心
退出
PostgreSQL 常用命令速查表
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構設計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
連接服務器
查看幫助
查看連接
查看版本
配置參數(shù)
退出客戶端
角色、用戶和組
創(chuàng)建角色
查看角色
修改密碼
設置密碼失效時間
用戶授權
查看權限
撤銷權限
設置當前角色
刪除角色
數(shù)據(jù)庫和模式
查看數(shù)據(jù)庫
創(chuàng)建數(shù)據(jù)庫
修改數(shù)據(jù)庫
刪除數(shù)據(jù)庫
查看模式
創(chuàng)建模式
修改模式
模式搜索路徑
刪除模式
管理數(shù)據(jù)表
創(chuàng)建表
查看所有表
查看表結構
增加字段
修改字段
重命名字段
刪除字段
增加約束
刪除約束
重命名表
刪除表
索引
創(chuàng)建索引
查看索引
維護索引
刪除索引
管理表空間
創(chuàng)建表空間
查看表空間
修改表空間
刪除表空間
備份與還原
使用 pg_dump 執(zhí)行邏輯備份
使用 psql/pg_restore 執(zhí)行還原
備份/還原整個數(shù)據(jù)庫集群
使用 COPY 導入/導出表數(shù)據(jù)
查詢語句
單表查詢
查詢條件
排序顯示
限定數(shù)量
分組操作
多表連接
子查詢
集合運算
通用表表達式
DML 語句
插入數(shù)據(jù)
更新數(shù)據(jù)
刪除數(shù)據(jù)
合并數(shù)據(jù)
事務控制
開始事務
提交事務
回滾事務
事務保存點
隔離級別
視圖
創(chuàng)建視圖
查看所有視圖
查看視圖定義
修改視圖
刪除視圖
存儲過程/函數(shù)
創(chuàng)建存儲過程/函數(shù)
調用存儲過程/函數(shù)
重命名存儲過程/函數(shù)
刪除存儲過程/函數(shù)
觸發(fā)器
創(chuàng)建觸發(fā)器
查看觸發(fā)器
修改觸發(fā)器
啟用/禁用觸發(fā)器
刪除觸發(fā)器
大家好，我是只談技術不剪發(fā)的 Tony 老師。本文為大家精心整理了 PostgreSQL 數(shù)據(jù)庫中最常用的語句和命令，點擊收藏以備不時之需！如果需要 PDF 版本，可以點此下載。
如果你點擊了收藏，也歡迎關注??、評論??、點贊??
連接服務器
使用 psql 客戶端工具連接 PostgreSQL 服務器的命令行如下：
psql -h hostname -p port -U username -W dbname
其中，hostname 表示服務器主機名，默認為本機；port 表示 PostgreSQL 實例服務端口，默認為 5432；username 表示用戶名，默認為當前操作系統(tǒng)用戶；-W 提示輸入密碼，默認行為；dbname 表示要連接的數(shù)據(jù)庫，默認和用戶名相同。例如：
[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 數(shù)據(jù)庫。
查看幫助
在 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".
查詢系統(tǒng)視圖 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 是客戶端的連接進程。
如果想要取消正在運行中的查詢，可以執(zhí)行以下語句：
SELECT pg_cancel_backend(pid);
其中，pid 是執(zhí)行語句的后臺進程 id，通過上文中的查詢可以獲得。
如果想要強制終止某個后臺連接進程，可以執(zhí)行以下語句：
SELECT pg_terminate_backend(pid);
其中，pid 是執(zhí)行語句的后臺進程 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)
配置參數(shù)
PostgreSQL 運行時的參數(shù)設置可以通過SHOW語句進行查看：
SHOW name
SHOW ALL
例如：
hrdb=> show shared_buffers;
shared_buffers
----------------
256MB
(1 row)
運行時的參數(shù)可以通過SET語句、修改 postgresql.conf 配置文件、設置 PGOPTIONS 環(huán)境變量（使用 libpq 或者基于 libpq應用連接）或者啟動服務時的命令行參數(shù)進行設置。
使用 SET 語句設置參數(shù)的命令如下：
SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT }
如果通過配置文件修改了參數(shù)，可以使用以下語句重新加載配置，而不需要重啟服務：
SELECT pg_reload_conf();
??關于 PostgreSQL 服務器參數(shù)的配置和優(yōu)化，可以參考這篇文章。
退出客戶端
使用exit、quit或者\q命令退出 psql 客戶端：
postgres=# \q
[root@sqlhost ~]#
??關于 PostgreSQL 客戶端工具 psql 的詳細使用，可以參考這篇文章。
角色、用戶和組
PostgreSQL 通過角色的概念來控制數(shù)據(jù)庫的訪問權限。角色又包含了兩種概念，具有登錄權限的角色稱為用戶，包含其他成員（也是角色）的角色稱為組（group）。因此，一個角色可以是一個用戶，也可以是一個組，或者兩者都是。
??PostgreSQL 角色與用戶管理的詳細內容可以參考這篇文章。
創(chuàng)建角色
使用CREATE ROLE語句創(chuàng)建一個角色：
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 屬性表示創(chuàng)建用戶，PASSWORD 屬性用于指定用戶密碼。例如：
CREATE ROLE tony LOGIN PASSWORD 'Pswd#123';
CREATE GROUP tony LOGIN PASSWORD 'Pswd#123';
CREATE USER tony PASSWORD 'Pswd#123';
以上語句創(chuàng)建了一個角色 tony，并且設置了登錄密碼。
??CREATE USER 和 CREATE GROUP 都是 CREATE ROLE 的同義詞，只是 CREATE USER 默認指定了 LOGIN 選項。
一個角色被創(chuàng)建之后，可以通過ALTER ROLE語句修改它的屬性。例如，以下命令為用戶 tony 設置了創(chuàng)建數(shù)據(jù)庫的權限：
ALTER ROLE tony createdb;
ALTER USER tony createdb;
查看角色
PostgreSQL 中的角色信息存儲在系統(tǒng)視圖 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 |
查詢結果中大部分都是系統(tǒng)創(chuàng)建的角色。另外，也可以通過 pg_catalog.pg_user 查看用戶信息，或者使用 psql 中的\du命令列出角色。
user 或者 current_user 函數(shù)可以用于查看當前的用戶：
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語句進行數(shù)據(jù)庫對象的授權操作。以表為例，基本的授權語法如下：
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 的成員。
查看權限
系統(tǒng)視圖 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語句撤銷數(shù)據(jù)庫對象上的權限。同樣以表為例，基本的撤銷授權語句如下：
REVOKE privilege_list | ALL
ON TABLE table_name
FROM role_name;
其中的參數(shù)與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 用戶的權限，不再擁有超級用戶權限?？梢允褂靡韵抡Z句要恢復初始狀態(tài)的會話權限：
SET ROLE NONE;
RESET ROLE;
刪除角色
刪除角色可以使用DROP ROLE語句。例如以下語句刪除了用戶 tony：
DROP ROLE IF EXISTS tony;
另外，DROP USER和DROP GROUP語句都是 DROP ROLE 的同義詞。
數(shù)據(jù)庫和模式
PostgreSQL 中的數(shù)據(jù)庫（Database）由一組相關的對象組成，例如表、索引、視圖、存儲過程等。數(shù)據(jù)庫中的對象使用模式（Schema）進行組織。因此，一個數(shù)據(jù)庫由多個模式組成，一個模式由許多對象組成。
??PostgreSQL 數(shù)據(jù)庫和模式管理的詳細內容可以參考這篇文章。
查看數(shù)據(jù)庫
使用 psql 工具的\l命令可以查看當前集群中的數(shù)據(jù)庫：
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 查看所有的數(shù)據(jù)庫：
SELECT datname
FROM pg_catalog.pg_database;
datname |
---------|
postgres |
template1|
template0|
pagila |
ds2 |
hrdb |
創(chuàng)建數(shù)據(jù)庫
PostgreSQL 使用CREATE DATABASE語句創(chuàng)建數(shù)據(jù)庫：
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 是數(shù)據(jù)庫的名稱，OWNER 用于指定數(shù)據(jù)庫的擁有者，TEMPLATE 是創(chuàng)建數(shù)據(jù)庫時使用的模板（默認使用 template1 數(shù)據(jù)庫），ENCODING 用于設置數(shù)據(jù)庫的字符集編碼。
例如，以下語句創(chuàng)建了一個名為 testdb 的數(shù)據(jù)庫：
CREATE DATABASE testdb;
修改數(shù)據(jù)庫
PostgreSQL 使用ALTER DATABASE語句修改數(shù)據(jù)庫的屬性和配置：
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
例如，以下語句將會默認禁用數(shù)據(jù)庫 newdb 中的索引掃描：
ALTER DATABASE newdb SET enable_indexscan TO off;
刪除數(shù)據(jù)庫
PostgreSQL 使用DROP DATABASE語句刪除數(shù)據(jù)庫，該數(shù)據(jù)庫中的所有對象以及與該數(shù)據(jù)庫相關的數(shù)據(jù)目錄也會被刪除：
DROP DATABASE [IF EXISTS] db_name;
我們將 newdb 數(shù)據(jù)庫和相關的數(shù)據(jù)刪除：
DROP DATABASE newdb;
如果存在任何目標為該數(shù)據(jù)庫的連接，無法執(zhí)行刪除操作。
查看模式
創(chuàng)建了數(shù)據(jù)庫之后，還需要創(chuàng)建模式才能夠存儲數(shù)據(jù)庫對象。通常在創(chuàng)建一個新的數(shù)據(jù)庫時，默認會創(chuàng)建一個公共模式 public。我們再次創(chuàng)建一個數(shù)據(jù)庫 testdb：
CREATE DATABASE testdb;
postgres=# \c testdb;
You are now connected to database "testdb" as user "postgres".
其中，\c用于連接數(shù)據(jù)庫。然后使用\dn查看當前數(shù)據(jù)庫中的模式：
testdb=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
另外，也可以系統(tǒng)視圖 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} |
查詢結果還返回了系統(tǒng)提供的其他模式。
創(chuàng)建模式
PostgreSQL 使用CREATE SCHEMA語句創(chuàng)建一個新的模式：
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_name ]
其中，schema_name 是模式名，role_name 是模式的擁有者，默認為執(zhí)行該語句的用戶。例如：
CREATE SCHEMA app AUTHORIZATION tony;
該語句創(chuàng)建了一個新的模式 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;
模式搜索路徑
當我們訪問數(shù)據(jù)表時，完整的對象名應該是 database.schema.table。例如：
SELECT count(*) FROM hrdb.public.employees;
為了方便書寫，常?？梢灾苯邮褂帽砻M行訪問，此時 PostgreSQL 按照預定義的搜索路徑查找不同模式下的對象。搜索路徑由不同的模式名稱組成，可以使用 SHOW 語句查看：
show search_path;
search_path
-----------------
"$user", public
(1 row)
默認的搜索路徑是與當前用戶同名的模式，加上 public 模式。不僅僅是對象查找，其他語句也會使用搜索路徑。例如 CREATE TABLE 默認會在 “$user” 模式下創(chuàng)建表，如果該模式不存在，則在 public 模式下創(chuàng)建。
可以使用 SET 命令修改搜索路徑，例如：
SET search_path TO sale,public;
刪除模式
使用DROP SCHEMA語句刪除模式：
DROP SCHEMA [ IF EXISTS ] schema_name [ CASCADE | RESTRICT ]
如果模式中存在對象，需要使用 CASCADE 級聯(lián)刪除；否則，無法刪除模式。例如，以下語句將會刪除模式 sale：
DROP SCHEMA sale;
管理數(shù)據(jù)表
??關于 PostgreSQL 表管理的詳細內容可以參考這篇文章。
創(chuàng)建表
PostgreSQL 使用CREATE TABLE語句創(chuàng)建表：
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 是可選的表級約束。
以下語句用于創(chuàng)建部門表和員工表：
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 還提供了另一個創(chuàng)建表的方法，就是通過一個查詢的結果創(chuàng)建新表：
CREATE TABLE [ IF NOT EXISTS ] table_name
AS query
[ WITH [ NO ] DATA ];
例如，我們可以基于 employees 復制出一個新的表：
CREATE TABLE emp1
AS
SELECT *
FROM employees;
emp1 只會復制 employees 的字段類型和名稱，以及查詢的結果，不會復制任何約束和索引。
查看所有表
在 psql 中使用\d命令可以查看當前數(shù)據(jù)庫中的所有表：
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;
添加字段與創(chuàng)建表時的字段選項相同，包含字段名稱、字段類型以及可選的約束。以下語句為 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 的數(shù)據(jù)類型修改為 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 選項，執(zhí)行級聯(lián)刪除。
索引
索引（Index）可以用于提高數(shù)據(jù)庫的查詢性能；但是索引也需要進行讀寫，同時還會占用更多的存儲空間；因此了解并適當利用索引對于數(shù)據(jù)庫的優(yōu)化至關重要。
??關于 PostgreSQL 索引和優(yōu)化的詳細內容可以參考這篇文章。
創(chuàng)建索引
PostgreSQL 為主鍵和唯一約束自動創(chuàng)建相應的索引，另外我們也可以手動創(chuàng)建索引。創(chuàng)建索引的命令如下：
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命令也可以用于查看數(shù)據(jù)庫中的索引列表。
維護索引
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 語句用于重建索引數(shù)據(jù)，支持不同級別的索引重建。
刪除索引
刪除索引使用以下命令：
DROP INDEX IF EXISTS index_name [ CASCADE | RESTRICT ];
CASCADE 表示級聯(lián)刪除其他依賴該索引的對象；RESTRICT（默認值）表示如果存在依賴于該索引的對象，將會拒絕刪除操作。
管理表空間
在 PostgreSQL 中，表空間（tablespace）表示數(shù)據(jù)文件的存放目錄，這些數(shù)據(jù)文件代表了數(shù)據(jù)庫的對象，例如表或索引。當我們訪問表時，系統(tǒng)通過它所在的表空間定位到對應數(shù)據(jù)文件所在的位置。
??PostgreSQL 表空間管理的詳細內容可以參考這篇文章。
創(chuàng)建表空間
創(chuàng)建新的表空間使用CREATE TABLESPACE語句：
CREATE TABLESPACE tablespace_name
OWNER user_name
LOCATION 'directory';
表空間的名稱不能以 ‘pg_’ 開頭，它們是系統(tǒng)表空間的保留名稱；LOCATION 參數(shù)必須指定絕對路徑名，指定的目錄必須是一個已經(jīng)存在的空目錄，PostgreSQL 操作系統(tǒng)用戶（postgres）必須是該目錄的擁有者，以便能夠進行文件的讀寫。
??PostgreSQL 支持在CREATE DATABASE、CREATE TABLE、CREATE INDEX 以及 ADD CONSTRAINT 語句中指定 tablespace_name 選項，覆蓋默認的表空間（pg_default）；也可以使用相應的 ALTER … 語句將對象從一個表空間移到另一個表空間。
查看表空間
系統(tǒng)視圖 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 [, ... ] );
第一個語句用于表空間的重命名；第二個語句用于修改表空間的擁有者；最后兩個語句用于設置表空間的參數(shù)。PostgreSQL 支持設置的表空間參數(shù)包括 seq_page_cost、random_page_cost 以及 effective_io_concurrency，它們用于查詢計劃器選擇執(zhí)行計劃時的代價評估。
刪除表空間
對于不再需要的表空間，可以使用DROP TABLESPACE語句進行刪除：
DROP TABLESPACE [ IF EXISTS ] tablespace_name;
IF EXISTS可以避免刪除不存在的表空間時產(chǎn)生錯誤信息。刪除表空間時，同時會刪除文件系統(tǒng)中對應的表空間子目錄。
備份與還原
??關于 PostgreSQL 備份與恢復的詳細介紹可以參考這篇文章。
使用 pg_dump 執(zhí)行邏輯備份
pg_dump 是 PostgreSQL 邏輯備份工具，用于導出創(chuàng)建數(shù)據(jù)庫（CREATE DATABASE）和插入數(shù)據(jù)的文本文件或者其他格式文件。
使用 pg_dump 文本格式備份指定數(shù)據(jù)庫的命令如下：
pg_dump db_name > file_name.sql
其中，db_name 表示數(shù)據(jù)庫名；file_name 表示備份文件名。
pg_dump 也可以選擇導出指定的表：
pg_dump -t 'table_name*' db_name > file_name.sql
以上命令表示導出數(shù)據(jù)庫 db_name 中名字以 table_name 開頭的表。
使用其他格式備份數(shù)據(jù)庫的語法如下：
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 執(zhí)行還原
對于 sql 文件格式的備份，可以使用 psql 還原數(shù)據(jù)庫：
psql newdb -f file_name.sql
pg_dump 和 psql 支持的讀寫管道功能使得我們可以直接將數(shù)據(jù)庫從一個服務器導出到另一個服務器，例如：
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
備份/還原整個數(shù)據(jù)庫集群
PostgreSQL 提供了導出數(shù)據(jù)庫集群的 pg_dumpall 工具。
pg_dumpall -f cluster.sql
pg_dumpall 導出 sql 文件格式的備份，還原時直接使用 psql 導入相關文件即可。
psql -f cluster.sql postgres
使用 COPY 導入/導出表數(shù)據(jù)
使用COPY命令可以導出單個表中的數(shù)據(jù)或查詢結果集：
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;
排除查詢結果中的重復數(shù)據(jù)：
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 同時還提供了不區(qū)分大小寫的[NOT] ILIKE運算符。
復雜條件
WHERE 子句可以包含多個條件，使用邏輯運算符（AND、OR、NOT）將它們進行組合，并根據(jù)最終的邏輯值進行過濾。對于AND運算符，只有當它兩邊的結果都為真時，最終結果才為真；否則最終結果為假，不返回結果。OR邏輯或運算符只要有一個條件為真，結果就為真。當我們組合 AND 和 OR 運算符時，AND 運算符優(yōu)先級更高。
排序顯示
??關于 PostgreSQL 的排序操作可以參考這篇文章。
指定排序字段的方式如下：
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 ASC, column2 DESC, ...;
PostgreSQL 支持使用 NULLS FIRST（空值排在最前）和 NULLS LAST（空值排在最后）指定空值的排序位置；升序排序時默認為 NULLS LAST，降序排序時默認為 NULLS FIRST。
限定數(shù)量
??關于 PostgreSQL Top-N 和分頁查詢的實現(xiàn)可以參考這篇文章。
PostgreSQL 支持 SQL 標準的 FETCH 和 OFFSET 子句，以及擴展的 LIMIT 語法限制返回結果的數(shù)量：
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;
常用的聚合函數(shù)：AVG、COUNT、MIN、MAX、SUM、STRING_AGG 等。PostgreSQL 除了支持基本的分組操作之外，還支持 3 種高級的分組選項：GROUPING SETS、ROLLUP 以及 CUBE。
多表連接
??關于 PostgreSQL 連接查詢這篇文章。
連接查詢用于從多個表中查詢關聯(lián)數(shù)據(jù)：
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 操作符與關聯(lián)子查詢：
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（默認值）表示去除查詢結果中的重復記錄。相同的集合操作符按照從左至右的順序執(zhí)行，INTERSECT 的優(yōu)先級高于 UNION 和 EXCEPT，使用括號可以修改集合操作的執(zhí)行順序。
通用表表達式
??關于PostgreSQL 通用表表達式的詳細內容可以參考這篇文章。
通用表表達式類似于派生表或者語句級別的視圖，但是可讀性和性能更好：
WITH cte_name (col1, col2, ...) AS (
cte_query_definition
)
sql_statement;
遞歸 CTE 允許在它的定義中進行自引用，以下查詢生成 1 到 10 的數(shù)字序列：
WITH RECURSIVE cte(n) AS (
SELECT 1 AS n -- 初始查詢
UNION ALL
SELECT n+1 FROM cte WHERE n < 10) -- 遞歸查詢
SELECT * FROM cte;
DML 語句
??關于 PostgreSQL 數(shù)據(jù)修改語句的使用可以參考這篇文章。
插入數(shù)據(jù)
插入數(shù)據(jù)使用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 返回插入的數(shù)據(jù)值：
INSERT INTO table_name(column1, column2, ...)
...
RETUNRING ...;
更新數(shù)據(jù)
PostgreSQL 使用UPDATE語句更新表中已有的數(shù)據(jù)：
UPDATE table_name
SET column1 = value1,
column2 = value2,
...
WHERE conditions;
其中，WHERE 決定了需要更新的數(shù)據(jù)行，只有滿足條件的數(shù)據(jù)才會更新；如果省略 WHERE 條件，將會更新表中的所有數(shù)據(jù)。
另外，PostgreSQL 還支持通過關聯(lián)其他表中的數(shù)據(jù)進行更新：
UPDATE table1
SET column1 = table2.salary,
column2 = value2,
...
FROM table2
WHERE conditions;
PostgreSQL 同樣對 UPDATE 語句進行了擴展，支持使用 RETURNING 返回更新后的數(shù)據(jù)值：
UPDATE table_name
SET ...
WHERE conditions
RETURNING ...;
刪除數(shù)據(jù)
刪除數(shù)據(jù)可以使用DELETE語句：
DELETE FROM table_name
WHERE conditions;
同樣，只有滿足WHERE條件的數(shù)據(jù)才會被刪除；如果省略，將會刪除表中所有的數(shù)據(jù)。
PostgreSQL 同樣支持通過關聯(lián)其他表進行數(shù)據(jù)刪除：
DELETE
FROM table1
USING table2
WHERE conditions;
DELETE 語句也可以使用 RETURNING 返回被刪除的數(shù)據(jù)：
DELETE FROM table_name
WHERE conditions
RETURNING ...;
另外，TRUNCATE TABLE語句用于快速清除表中的全部數(shù)據(jù)：
TRUNCATE TABLE table_name;
合并數(shù)據(jù)
PostgreSQL 可以通過INSERT INTO ON CONFLICT語句實現(xiàn)數(shù)據(jù)合并（MERGE）的功能：
INSERT INTO table_name(column1, column2, ...)
...
ON CONFLICT [conflict_target]
{DO NOTHING | DO UPDATE SET ... [WHERE contidions]};
其中，conflict_target 是判斷數(shù)據(jù)是否存在沖突的條件：
( { index_column_name | ( index_expression ) } )，基于某個具有索引的字段或者表達式進行判斷；
ON CONSTRAINT constraint_name，基于某個唯一約束進行判斷。
事務控制
??關于 PostgreSQL 數(shù)據(jù)庫事務和事務控制語句可以參考這篇文章。
開始事務
默認情況下，PostgreSQL 執(zhí)行任何語句都會自動開始一個事務并提交該事務。如果有需要，可以使用BEGIN語句手動開始一個事務：
BEGIN;
另外，也可以使用BEGIN WORK或者BEGIN TRANSACTION開始事務。
提交事務
PostgreSQL 使用COMMIT語句提交已經(jīng)打開的事務：
COMMIT;
另外，也可以使用COMMIT WORK或者COMMIT TRANSACTION提交事務。
回滾事務
ROLLBACK用于回滾當前事務：
ROLLBACK;
另外，也可以使用ROLLBACK WORK或者ROLLBACK TRANSACTION回滾事務。
事務保存點
事務保存點可以用于回滾部分事務，SAVEPOINT語句用于在事務中定義保存點：
BEGIN;
...
SAVEPOINT savepoint_name;
...
ROLLBACK TO SAVEPOINT savepoint_name;
...
COMMIT;
其中，ROLLBACK TO 用于回退到保存點時的狀態(tài)。另外，RELEASE SAVEPOINT savepoint_name可以釋放保存點。
隔離級別
使用 SHOW 命令可以查看當前的隔離級別：
SHOW transaction_isolation;
如果需要修改當前事務的隔離級別，可以在事務的最開始執(zhí)行SET TRANSACTION命令：
BEGIN;
SET TRANSACTION ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED };
...
視圖
??關于 PostgreSQL 視圖的概念和使用，可以參考這篇文章。
創(chuàng)建視圖
PostgreSQL 使用CREATE VIEW語句創(chuàng)建視圖：
CREATE [OR REPLACE] VIEW view_name
AS
select-statement
WITH CHECK OPTION;
WITH CHECK OPTION 選項可以阻止通過視圖修改或者插入視圖范圍之外的基礎表數(shù)據(jù)。
查看所有視圖
PostgreSQL 系統(tǒng)表 information_schema.views 中存儲了關于視圖的所有信息：
SELECT *
FROM information_schema.views;
psql 命令\dv也可以列出當前數(shù)據(jù)庫中的所有視圖。
查看視圖定義
查看視圖的定義：
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;
存儲過程/函數(shù)
??關于 PostgreSQL 存儲過程/函數(shù)的定義和使用，可以參考這篇文章。
創(chuàng)建存儲過程/函數(shù)
使用CREATE PROCEDURE語句創(chuàng)建PL/pgSQL 存儲過程：
CREATE [ OR REPLACE ] PROCEDURE
procedure_name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
AS $$
DECLARE
declarations
BEGIN
statements;
...
END; $$
LANGUAGE plpgsql;
使用CREATE FUNCTION語句創(chuàng)建PL/pgSQL 函數(shù)：
CREATE [ OR REPLACE ] FUNCTION
function_name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
RETURNS rettype
AS $$
DECLARE
declarations
BEGIN
statements;
...
END; $$
LANGUAGE plpgsql;
調用存儲過程/函數(shù)
調用存儲過程使用CALL語句：
CALL procedure_name( argument1, ... );
存儲函數(shù)可以像可以像內置函數(shù)一樣在 SQL 語句中進行調用：
SELECT function_name( argument1, ... );
重命名存儲過程/函數(shù)
使用ALTER PROCEDURE和ALTER FUNCTION語句修改存儲過程/函數(shù)的屬性，例如修改名稱：
ALTER PROCEDURE procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
RENAME TO new_name;
ALTER FUNCTION function_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
RENAME TO new_name;
刪除存儲過程/函數(shù)
刪除存儲過程/函數(shù)使用DROP語句：
DROP PROCEDURE IF EXISTS procedure_name [ CASCADE | RESTRICT ];
DROP FUNCTION IF EXISTS function_name [ CASCADE | RESTRICT ];
觸發(fā)器
??關于 PostgreSQL 觸發(fā)器的詳細介紹，可以參考這篇文章。
創(chuàng)建觸發(fā)器
PostgreSQL 觸發(fā)器的創(chuàng)建分為兩步：首先，使用CREATE FUNCTION語句創(chuàng)建一個觸發(fā)器函數(shù)；然后，使用CREATE TRIGGER語句將該函數(shù)與表進行關聯(lián)。
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）的更新操作；觸發(fā)器可以在事件之前（BEFORE）或者之后（AFTER）觸發(fā)，INSTEAD OF 只能用于替代視圖上的 INSERT、UPDATE 或者 DELETE 操作；FOR EACH ROW 表示行級觸發(fā)器，F(xiàn)OR EACH STATEMENT 表示語句級觸發(fā)器；WHEN 用于指定一個額外的觸發(fā)條件，滿足條件才會真正支持觸發(fā)器函數(shù)。
查看觸發(fā)器
視圖 information_schema.triggers 中存儲了關于觸發(fā)器的信息：
SELECT *
FROM information_schema.triggers;
修改觸發(fā)器
PostgreSQL 使用ALTER TRIGGER語句修改觸發(fā)器，目前只支持修改觸發(fā)器的名稱：
ALTER TRIGGER trigger_name ON table_name RENAME TO new_name;
啟用/禁用觸發(fā)器
PostgreSQL 支持觸發(fā)器的禁用和啟用：
ALTER TABLE table_name
{ENABLE | DISABLE} TRIGGER {trigger_name | ALL | USER};
刪除觸發(fā)器
刪除觸發(fā)器的語句如下：
DROP TRIGGER [IF EXISTS] trigger_name
ON table_name [RESTRICT | CASCADE];
分類導航