PostgreSQL 常用命令速查表
大家好，我是隻談技術不剪髮的 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];
