PostgreSQL 语法

日期：2022-07-07

本文字数：2,220 字阅读完需：约 12 分钟

PostgreSQL 语法

默认情况下 PostgreSQL 安装完成后，自带了一个命令行工具 SQL Shell(psql)。

Linux 系统可以直接切换到 postgres 用户来开启命令行工具：

1#  sudo -i -u postgres

Windows 系统一般在它的安装目录下：

Program Files → PostgreSQL 11.3 → SQL Shell(psql)

Mac OS 我们直接搜索就可以了找到：

进入命令行工具，我们可以使用 \help 来查看各个命令的语法：

1postgres-# \help <command_name>

例如，我们查看下 select 语句的语法：

1postgres=# \help SELECT
2Command:     SELECT
3Description: retrieve rows from a table or view
4Syntax:
5[ WITH [ RECURSIVE ] with_query [, ...] ]
6SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
7    [ * | expression [ [ AS ] output_name ] [, ...] ]
8    [ FROM from_item [, ...] ]
9    [ WHERE condition ]
10    [ GROUP BY grouping_element [, ...] ]
11    [ HAVING condition [, ...] ]
12    [ WINDOW window_name AS ( window_definition ) [, ...] ]
13    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
14    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
15    [ LIMIT { count | ALL } ]
16    [ OFFSET start [ ROW | ROWS ] ]
17    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
18    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
19
20from_item 可以是以下选项之一：
21
22    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]

SQL 语句

一个 SQL 语句通常包含了关键字、标识符（字段）、常量、特殊符号等，下面是一个简单的 SQL 语句：

1SELECT id, name FROM runoob

	SELECT	id, name	FROM	runoob
符号类型	关键字	标识符（字段）	关键字	标识符
描述	命令	id 和 name 字段	语句，用于设置条件规则等	表名

PostgreSQL 命令

ABORT

ABORT 用于退出当前事务。

1ABORT [ WORK | TRANSACTION ]

ALTER AGGREGATE

修改一个聚集函数的定义。

1ALTER AGGREGATE _name_ ( _argtype_ [ , ... ] ) RENAME TO _new_name_
2ALTER AGGREGATE _name_ ( _argtype_ [ , ... ] ) OWNER TO _new_owner_
3ALTER AGGREGATE _name_ ( _argtype_ [ , ... ] ) SET SCHEMA _new_schema_

ALTER COLLATION

修改一个排序规则定义。

1ALTER COLLATION _name_ RENAME TO _new_name_
2ALTER COLLATION _name_ OWNER TO _new_owner_
3ALTER COLLATION _name_ SET SCHEMA _new_schema_

ALTER CONVERSION

修改一个编码转换的定义。

1ALTER CONVERSION name RENAME TO new_name
2ALTER CONVERSION name OWNER TO new_owner

ALTER DATABASE

修改一个数据库。

1ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }
2ALTER DATABASE name RESET parameter
3ALTER DATABASE name RENAME TO new_name
4ALTER DATABASE name OWNER TO new_owner

ALTER DEFAULT PRIVILEGES

定义默认的访问权限。

1ALTER DEFAULT PRIVILEGES
2    [ FOR { ROLE | USER } target_role [, ...] ]
3    [ IN SCHEMA schema_name [, ...] ]
4    abbreviated_grant_or_revoke
5
6where abbreviated_grant_or_revoke is one of:
7
8GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
9    [, ...] | ALL [ PRIVILEGES ] }
10    ON TABLES
11    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
12...

ALTER DOMAIN

修改一个域的定义。

1ALTER DOMAIN name { SET DEFAULT expression | DROP DEFAULT }
2ALTER DOMAIN name { SET | DROP } NOT NULL
3ALTER DOMAIN name ADD domain_constraint
4ALTER DOMAIN name DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
5ALTER DOMAIN name OWNER TO new_owner

ALTER FUNCTION

修改一个函数的定义。

1ALTER FUNCTION name ( [ type [, ...] ] ) RENAME TO new_name
2ALTER FUNCTION name ( [ type [, ...] ] ) OWNER TO new_owner

ALTER GROUP

修改一个用户组。

1ALTER GROUP groupname ADD USER username [, ... ]
2ALTER GROUP groupname DROP USER username [, ... ]
3ALTER GROUP groupname RENAME TO new_name

ALTER INDEX

修改一个索引的定义。

1ALTER INDEX name OWNER TO new_owner
2ALTER INDEX name SET TABLESPACE indexspace_name
3ALTER INDEX name RENAME TO new_name

ALTER LANGUAGE

修改一个过程语言的定义。

1ALTER LANGUAGE name RENAME TO new_name

ALTER OPERATOR

改变一个操作符的定义。

1ALTER OPERATOR name ( { lefttype | NONE }, { righttype | NONE } )
2OWNER TO new_owner

ALTER OPERATOR CLASS

修改一个操作符表的定义。

1ALTER OPERATOR CLASS name USING index_method RENAME TO new_name
2ALTER OPERATOR CLASS name USING index_method OWNER TO new_owner

ALTER SCHEMA

修改一个模式的定义。

1ALTER SCHEMA name RENAME TO new_name
2ALTER SCHEMA name OWNER TO new_owner

ALTER SEQUENCE

修改一个序列生成器的定义。

1ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
2[ MINVALUE minvalue | NO MINVALUE ]
3[ MAXVALUE maxvalue | NO MAXVALUE ]
4[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

ALTER TABLE

修改表的定义。

1ALTER TABLE [ ONLY ] name [ * ]
2action [, ... ]
3ALTER TABLE [ ONLY ] name [ * ]
4RENAME [ COLUMN ] column TO new_column
5ALTER TABLE name
6RENAME TO new_name

其中 action 可以是以选项之一：

1ADD [ COLUMN ] column_type [ column_constraint [ ... ] ]
2DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
3ALTER [ COLUMN ] column TYPE type [ USING expression ]
4ALTER [ COLUMN ] column SET DEFAULT expression
5ALTER [ COLUMN ] column DROP DEFAULT
6ALTER [ COLUMN ] column { SET | DROP } NOT NULL
7ALTER [ COLUMN ] column SET STATISTICS integer
8ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
9ADD table_constraint
10DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
11CLUSTER ON index_name
12SET WITHOUT CLUSTER
13SET WITHOUT OIDS
14OWNER TO new_owner
15SET TABLESPACE tablespace_name

ALTER TABLESPACE

修改一个表空间的定义。

1ALTER TABLESPACE name RENAME TO new_name
2ALTER TABLESPACE name OWNER TO new_owner

ALTER TRIGGER

修改改变一个触发器的定义。

1ALTER TRIGGER name ON table RENAME TO new_name

ALTER TYPE

修改一个类型的定义。

1ALTER TYPE name OWNER TO new_owner

ALTER USER

修改数据库用户帐号。

1ALTER USER name [ [ WITH ] option [ ... ] ]
2ALTER USER name RENAME TO new_name
3ALTER USER name SET parameter { TO | = } { value | DEFAULT }
4ALTER USER name RESET parameter

Where option can be −

1[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
2| CREATEDB | NOCREATEDB
3| CREATEUSER | NOCREATEUSER
4| VALID UNTIL 'abstime'

ANALYZE

收集与数据库有关的统计。

1ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]

BEGIN

开始一个事务块。

1BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]

transaction_mode 可以是以下选项之一：

1ISOLATION LEVEL { 
2   SERIALIZABLE | REPEATABLE READ | READ COMMITTED
3   | READ UNCOMMITTED
4}
5READ WRITE | READ ONLY

CHECKPOINT

强制一个事务日志检查点。

1CHECKPOINT

CLOSE

关闭游标。

1CLOSE name

CLUSTER

根据一个索引对某个表盘簇化排序。

1CLUSTER index_name ON table_name
2CLUSTER table_name
3CLUSTER

COMMENT

定义或者改变一个对象的注释。

1COMMENT ON {
2   TABLE object_name |
3   COLUMN table_name.column_name |
4   AGGREGATE agg_name (agg_type) |
5   CAST (source_type AS target_type) |
6   CONSTRAINT constraint_name ON table_name |
7   CONVERSION object_name |
8   DATABASE object_name |
9   DOMAIN object_name |
10   FUNCTION func_name (arg1_type, arg2_type, ...) |
11   INDEX object_name |
12   LARGE OBJECT large_object_oid |
13   OPERATOR op (left_operand_type, right_operand_type) |
14   OPERATOR CLASS object_name USING index_method |
15   [ PROCEDURAL ] LANGUAGE object_name |
16   RULE rule_name ON table_name |
17   SCHEMA object_name |
18   SEQUENCE object_name |
19   TRIGGER trigger_name ON table_name |
20   TYPE object_name |
21   VIEW object_name
22} 
23IS 'text'

COMMIT

提交当前事务。

1COMMIT [ WORK | TRANSACTION ]

COPY

在表和文件之间拷贝数据。

1COPY table_name [ ( column [, ...] ) ]
2FROM { 'filename' | STDIN }
3[ WITH ]
4[ BINARY ]
5[ OIDS ]
6[ DELIMITER [ AS ] 'delimiter' ]
7[ NULL [ AS ] 'null string' ]
8[ CSV [ QUOTE [ AS ] 'quote' ]
9[ ESCAPE [ AS ] 'escape' ]
10[ FORCE NOT NULL column [, ...] ]
11COPY table_name [ ( column [, ...] ) ]
12TO { 'filename' | STDOUT }
13[ [ WITH ]
14[ BINARY ]
15[ OIDS ]
16[ DELIMITER [ AS ] 'delimiter' ]
17[ NULL [ AS ] 'null string' ]
18[ CSV [ QUOTE [ AS ] 'quote' ]
19[ ESCAPE [ AS ] 'escape' ]
20[ FORCE QUOTE column [, ...] ]

CREATE AGGREGATE

定义一个新的聚集函数。

1CREATE AGGREGATE name (
2   BASETYPE = input_data_type,
3   SFUNC = sfunc,
4   STYPE = state_data_type
5   [, FINALFUNC = ffunc ]
6   [, INITCOND = initial_condition ]
7)

CREATE CAST

定义一个用户定义的转换。

1CREATE CAST (source_type AS target_type)
2WITH FUNCTION func_name (arg_types)
3[ AS ASSIGNMENT | AS IMPLICIT ]
4CREATE CAST (source_type AS target_type)
5WITHOUT FUNCTION
6[ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CONSTRAINT TRIGGER

定义一个新的约束触发器。

1CREATE CONSTRAINT TRIGGER name
2AFTER events ON
3table_name constraint attributes
4FOR EACH ROW EXECUTE PROCEDURE func_name ( args )

CREATE CONVERSION

定义一个新的的编码转换。

1CREATE [DEFAULT] CONVERSION name
2FOR source_encoding TO dest_encoding FROM func_name

CREATE DATABASE

创建新数据库。

1CREATE DATABASE name
2[ [ WITH ] [ OWNER [=] db_owner ]
3   [ TEMPLATE [=] template ]
4   [ ENCODING [=] encoding ]
5   [ TABLESPACE [=] tablespace ] 
6]

CREATE DOMAIN

定义一个新域。

1CREATE DOMAIN name [AS] data_type
2[ DEFAULT expression ]
3[ constraint [ ... ] ]

constraint 可以是以下选项之一：

1[ CONSTRAINT constraint_name ]
2{ NOT NULL | NULL | CHECK (expression) }

CREATE FUNCTION

定义一个新函数。

1CREATE [ OR REPLACE ] FUNCTION name ( [ [ arg_name ] arg_type [, ...] ] )
2RETURNS ret_type
3{ LANGUAGE lang_name
4   | IMMUTABLE | STABLE | VOLATILE
5   | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
6   | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
7   | AS 'definition'
8   | AS 'obj_file', 'link_symbol'
9} ...
10[ WITH ( attribute [, ...] ) ]

CREATE GROUP

定义一个新的用户组。

1CREATE GROUP name [ [ WITH ] option [ ... ] ]
2Where option can be:
3SYSID gid
4| USER username [, ...]

CREATE INDEX

定义一个新索引。

1CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
2( { column | ( expression ) } [ opclass ] [, ...] )
3[ TABLESPACE tablespace ]
4[ WHERE predicate ]

CREATE LANGUAGE

定义一种新的过程语言。

1CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
2HANDLER call_handler [ VALIDATOR val_function ]

CREATE OPERATOR

定义一个新的操作符。

1CREATE OPERATOR name (
2   PROCEDURE = func_name
3   [, LEFTARG = left_type ] [, RIGHTARG = right_type ]
4   [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
5   [, RESTRICT = res_proc ] [, JOIN = join_proc ]
6   [, HASHES ] [, MERGES ]
7   [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ]
8   [, LTCMP = less_than_op ] [, GTCMP = greater_than_op ]
9)

CREATE OPERATOR CLASS

定义一个新的操作符表。

1CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type
2USING index_method AS
3{ OPERATOR strategy_number operator_name [ ( op_type, op_type ) ] [ RECHECK ]
4   | FUNCTION support_number func_name ( argument_type [, ...] )
5   | STORAGE storage_type
6} [, ... ]

CREATE ROLE

定义一个新的数据库角色。

1CREATE ROLE _name_ [ [ WITH ] _option_ [ ... ] ]
2
3where `_option_` can be:
4
5      SUPERUSER | NOSUPERUSER
6    | CREATEDB | NOCREATEDB
7    | CREATEROLE | NOCREATEROLE
8...

CREATE RULE

定义一个新重写规则。

1CREATE [ OR REPLACE ] RULE name AS ON event
2TO table [ WHERE condition ]
3DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

CREATE SCHEMA

定义一个新模式。

1CREATE SCHEMA schema_name
2[ AUTHORIZATION username ] [ schema_element [ ... ] ]
3CREATE SCHEMA AUTHORIZATION username
4[ schema_element [ ... ] ]

CREATE SERVER

定义一个新的外部服务器。。

1CREATE SERVER _server_name_ [ TYPE '_server_type_' ] [ VERSION '_server_version_' ]
2    FOREIGN DATA WRAPPER _fdw_name_
3    [ OPTIONS ( _option_ '_value_' [, ... ] ) ]

CREATE SEQUENCE

定义一个新序列发生器。

1CREATE [ TEMPORARY | TEMP ] SEQUENCE name
2[ INCREMENT [ BY ] increment ]
3[ MINVALUE minvalue | NO MINVALUE ]
4[ MAXVALUE maxvalue | NO MAXVALUE ]
5[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

CREATE TABLE

定义一个新表。

1CREATE [ [ GLOBAL | LOCAL ] { 
2   TEMPORARY | TEMP } ] TABLE table_name ( { 
3      column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
4      | table_constraint
5      | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] 
6   } [, ... ]
7)
8[ INHERITS ( parent_table [, ... ] ) ]
9[ WITH OIDS | WITHOUT OIDS ]
10[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
11[ TABLESPACE tablespace ]

column_constraint 可以是以下选项之一：

1[ CONSTRAINT constraint_name ] { 
2   NOT NULL |
3   NULL |
4   UNIQUE [ USING INDEX TABLESPACE tablespace ] |
5   PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
6   CHECK (expression) |
7   REFERENCES ref_table [ ( ref_column ) ]
8   [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
9   [ ON DELETE action ] [ ON UPDATE action ] 
10}
11[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

table_constraint 可以是以下选项之一：

1[ CONSTRAINT constraint_name ]
2{ UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
3PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
4CHECK ( expression ) |
5FOREIGN KEY ( column_name [, ... ] )
6REFERENCES ref_table [ ( ref_column [, ... ] ) ]
7[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
8[ ON DELETE action ] [ ON UPDATE action ] }
9[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

CREATE TABLE AS

从一条查询的结果中定义一个新表。

1CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
2[ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
3AS query

CREATE TABLESPACE

定义一个新的表空间。

1CREATE TABLESPACE tablespace_name [ OWNER username ] LOCATION 'directory'

CREATE TRIGGER

定义一个新的触发器。

1CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
2ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
3EXECUTE PROCEDURE func_name ( arguments )

CREATE TYPE

定义一个新的数据类型。

1CREATE TYPE name AS
2( attribute_name data_type [, ... ] )
3CREATE TYPE name (
4INPUT = input_function,
5OUTPUT = output_function
6[, RECEIVE = receive_function ]
7[, SEND = send_function ]
8[, ANALYZE = analyze_function ]
9[, INTERNALLENGTH = { internal_length | VARIABLE } ]
10[, PASSEDBYVALUE ]
11[, ALIGNMENT = alignment ]
12[, STORAGE = storage ]
13[, DEFAULT = default ]
14[, ELEMENT = element ]
15[, DELIMITER = delimiter ]
16)

CREATE USER

创建一个新的数据库用户帐户。

1CREATE USER name [ [ WITH ] option [ ... ] ]

option 可以是以下选项之一：

1SYSID uid
2| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
3| CREATEDB | NOCREATEDB
4| CREATEUSER | NOCREATEUSER
5| IN GROUP group_name [, ...]
6| VALID UNTIL 'abs_time'

CREATE VIEW

定义一个视图。

1CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query

DEALLOCATE

删除一个准备好的查询。

1DEALLOCATE [ PREPARE ] plan_name

DECLARE

定义一个游标。

1DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
2CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
3[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]

DELETE

删除一个表中的行。

1DELETE FROM [ ONLY ] table [ WHERE condition ]

DROP AGGREGATE

删除一个用户定义的聚集函数。

1DROP AGGREGATE name ( type ) [ CASCADE | RESTRICT ]

DROP CAST

删除一个用户定义的类型转换。

1DROP CAST (source_type AS target_type) [ CASCADE | RESTRICT ]

DROP CONVERSION

删除一个用户定义的编码转换。

1DROP CONVERSION name [ CASCADE | RESTRICT ]

DROP DATABASE

删除一个数据库。

1DROP DATABASE name

DROP DOMAIN

删除一个用户定义的域。

1DROP DOMAIN name [, ...] [ CASCADE | RESTRICT ]

DROP FUNCTION

删除一个函数。

1DROP FUNCTION name ( [ type [, ...] ] ) [ CASCADE | RESTRICT ]

DROP GROUP

删除一个用户组。

1DROP GROUP name

DROP INDEX

删除一个索引。

1DROP INDEX name [, ...] [ CASCADE | RESTRICT ]

DROP LANGUAGE

删除一个过程语言。

1DROP [ PROCEDURAL ] LANGUAGE name [ CASCADE | RESTRICT ]

DROP OPERATOR

删除一个操作符。

1DROP OPERATOR name ( { left_type | NONE }, { right_type | NONE } )
2[ CASCADE | RESTRICT ]

DROP OPERATOR CLASS

删除一个操作符表。

1DROP OPERATOR CLASS name USING index_method [ CASCADE | RESTRICT ]

DROP ROLE

删除一个数据库角色。

1DROP ROLE [ IF EXISTS ] _name_ [, ...]

DROP RULE

删除一个重写规则。

1DROP RULE name ON relation [ CASCADE | RESTRICT ]

DROP SCHEMA

删除一个模式。

1DROP SCHEMA name [, ...] [ CASCADE | RESTRICT ]

DROP SEQUENCE

删除一个序列。

1DROP SEQUENCE name [, ...] [ CASCADE | RESTRICT ]

DROP TABLE

删除一个表。

1DROP TABLE name [, ...] [ CASCADE | RESTRICT ]

DROP TABLESPACE

删除一个表空间。

1DROP TABLESPACE tablespace_name

DROP TRIGGER

删除一个触发器定义。

1DROP TRIGGER name ON table [ CASCADE | RESTRICT ]

DROP TYPE

删除一个用户定义数据类型。

1DROP TYPE name [, ...] [ CASCADE | RESTRICT ]

DROP USER

删除一个数据库用户帐号。

1DROP USER name

DROP VIEW

删除一个视图。

1DROP VIEW name [, ...] [ CASCADE | RESTRICT ]

END

提交当前的事务。

1END [ WORK | TRANSACTION ]

EXECUTE

执行一个准备好的查询。

1EXECUTE plan_name [ (parameter [, ...] ) ]

EXPLAIN

显示一个语句的执行规划。

1EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

FETCH

用游标从查询中抓取行。

1FETCH [ direction { FROM | IN } ] cursor_name

direction 可以是以下选项之一：

1NEXT
2PRIOR
3FIRST
4LAST
5ABSOLUTE count
6RELATIVE count
7count
8ALL
9FORWARD
10FORWARD count
11FORWARD ALL
12BACKWARD
13BACKWARD count
14BACKWARD ALL

GRANT

定义访问权限。

1GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
2[,...] | ALL [ PRIVILEGES ] }
3ON [ TABLE ] table_name [, ...]
4TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
5
6GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
7ON DATABASE db_name [, ...]
8TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
9
10GRANT { CREATE | ALL [ PRIVILEGES ] }
11ON TABLESPACE tablespace_name [, ...]
12TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
13
14GRANT { EXECUTE | ALL [ PRIVILEGES ] }
15ON FUNCTION func_name ([type, ...]) [, ...]
16TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
17
18GRANT { USAGE | ALL [ PRIVILEGES ] }
19ON LANGUAGE lang_name [, ...]
20TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
21
22GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
23ON SCHEMA schema_name [, ...]
24TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

INSERT

在表中创建新行，即插入数据。

1INSERT INTO table [ ( column [, ...] ) ]
2{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }

LISTEN

监听一个通知。

1LISTEN name

LOAD

加载或重载一个共享库文件。

1LOAD 'filename'

LOCK

锁定一个表。

1LOCK [ TABLE ] name [, ...] [ IN lock_mode MODE ] [ NOWAIT ]

lock_mode 可以是以下选项之一：

1ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
2| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

MOVE

定位一个游标。

1MOVE [ direction { FROM | IN } ] cursor_name

NOTIFY

生成一个通知。

1NOTIFY name

PREPARE

创建一个准备好的查询。

1PREPARE plan_name [ (data_type [, ...] ) ] AS statement

REINDEX

重建索引。

1REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]

RELEASE SAVEPOINT

删除一个前面定义的保存点。

1RELEASE [ SAVEPOINT ] savepoint_name

RESET

把一个运行时参数值恢复为默认值。

1RESET name
2RESET ALL

REVOKE

删除访问权限。

1REVOKE [ GRANT OPTION FOR ]
2{ { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
3[,...] | ALL [ PRIVILEGES ] }
4ON [ TABLE ] table_name [, ...]
5FROM { username | GROUP group_name | PUBLIC } [, ...]
6[ CASCADE | RESTRICT ]
7
8REVOKE [ GRANT OPTION FOR ]
9{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
10ON DATABASE db_name [, ...]
11FROM { username | GROUP group_name | PUBLIC } [, ...]
12[ CASCADE | RESTRICT ]
13
14REVOKE [ GRANT OPTION FOR ]
15{ CREATE | ALL [ PRIVILEGES ] }
16ON TABLESPACE tablespace_name [, ...]
17FROM { username | GROUP group_name | PUBLIC } [, ...]
18[ CASCADE | RESTRICT ]
19
20REVOKE [ GRANT OPTION FOR ]
21{ EXECUTE | ALL [ PRIVILEGES ] }
22ON FUNCTION func_name ([type, ...]) [, ...]
23FROM { username | GROUP group_name | PUBLIC } [, ...]
24[ CASCADE | RESTRICT ]
25
26REVOKE [ GRANT OPTION FOR ]
27{ USAGE | ALL [ PRIVILEGES ] }
28ON LANGUAGE lang_name [, ...]
29FROM { username | GROUP group_name | PUBLIC } [, ...]
30[ CASCADE | RESTRICT ]
31
32REVOKE [ GRANT OPTION FOR ]
33{ { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
34ON SCHEMA schema_name [, ...]
35FROM { username | GROUP group_name | PUBLIC } [, ...]
36[ CASCADE | RESTRICT ]

ROLLBACK

退出当前事务。

1ROLLBACK [ WORK | TRANSACTION ]

ROLLBACK TO SAVEPOINT

回滚到一个保存点。

1ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name

SAVEPOINT

在当前事务里定义一个新的保存点。

1SAVEPOINT savepoint_name

SELECT

从表或视图中取出若干行。

1SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
2* | expression [ AS output_name ] [, ...]
3[ FROM from_item [, ...] ]
4[ WHERE condition ]
5[ GROUP BY expression [, ...] ]
6[ HAVING condition [, ...] ]
7[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
8[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
9[ LIMIT { count | ALL } ]
10[ OFFSET start ]
11[ FOR UPDATE [ OF table_name [, ...] ] ]

from_item 可以是以下选项：

1[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
2( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
3function_name ( [ argument [, ...] ] )
4[ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
5function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
6from_item [ NATURAL ] join_type from_item
7[ ON join_condition | USING ( join_column [, ...] ) ]

SELECT INTO

从一个查询的结果中定义一个新表。

1SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
2* | expression [ AS output_name ] [, ...]
3INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table
4[ FROM from_item [, ...] ]
5[ WHERE condition ]
6[ GROUP BY expression [, ...] ]
7[ HAVING condition [, ...] ]
8[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
9[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
10[ LIMIT { count | ALL } ]
11[ OFFSET start ]
12[ FOR UPDATE [ OF table_name [, ...] ] ]

SET

修改运行时参数。

1SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' | DEFAULT }
2SET [ SESSION | LOCAL ] TIME ZONE { time_zone | LOCAL | DEFAULT }

SET CONSTRAINTS

设置当前事务的约束检查模式。

1SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }

SET SESSION AUTHORIZATION

为当前会话设置会话用户标识符和当前用户标识符。

1SET [ SESSION | LOCAL ] SESSION AUTHORIZATION username
2SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT
3RESET SESSION AUTHORIZATION

SET TRANSACTION

开始一个事务块。

1SET TRANSACTION transaction_mode [, ...]
2SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]

Where transaction_mode is one of −

1ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED
2| READ UNCOMMITTED }
3READ WRITE | READ ONLY

SHOW

显示运行时参数的值。

1SHOW name
2SHOW ALL

START TRANSACTION

开始一个事务块。

1START TRANSACTION [ transaction_mode [, ...] ]

transaction_mode 可以是下面的选项之一：

1ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED
2| READ UNCOMMITTED }
3READ WRITE | READ ONLY

TRUNCATE

清空一个或一组表。

1TRUNCATE [ TABLE ] name

UNLISTEN

停止监听通知信息。

1UNLISTEN { name | * }

UPDATE

更新一个表中的行。

1UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
2[ FROM from_list ]
3[ WHERE condition ]

VACUUM

垃圾收集以及可选地分析一个数据库。

1VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
2VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

VALUES

计算一个或一组行。

1VALUES ( _expression_ [, ...] ) [, ...]
2    [ ORDER BY _sort_expression_ [ ASC | DESC | USING _operator_ ] [, ...] ]
3    [ LIMIT { _count_ | ALL } ]
4    [ OFFSET _start_ [ ROW | ROWS ] ]
5    [ FETCH { FIRST | NEXT } [ _count_ ] { ROW | ROWS } ONLY ]

更多内容可以参考手册中的 SQL 语法：http://www.runoob.com/manual/PostgreSQL/sql-syntax.html。

share.png

PostgreSQL 语法

PostgreSQL 语法

PostgreSQL 语法

SQL 语句

PostgreSQL 命令

ABORT

ALTER AGGREGATE

ALTER COLLATION

ALTER CONVERSION

ALTER DATABASE

ALTER DEFAULT PRIVILEGES

ALTER DOMAIN

ALTER FUNCTION

ALTER GROUP

ALTER INDEX

ALTER LANGUAGE

ALTER OPERATOR

ALTER OPERATOR CLASS

ALTER SCHEMA

ALTER SEQUENCE

ALTER TABLE

ALTER TABLESPACE

ALTER TRIGGER

ALTER TYPE

ALTER USER

ANALYZE

BEGIN

CHECKPOINT

CLOSE

CLUSTER

COMMENT

COMMIT

COPY

CREATE AGGREGATE

CREATE CAST

CREATE CONSTRAINT TRIGGER

CREATE CONVERSION

CREATE DATABASE

CREATE DOMAIN

CREATE FUNCTION

CREATE GROUP

CREATE INDEX

CREATE LANGUAGE

CREATE OPERATOR

CREATE OPERATOR CLASS

CREATE ROLE

CREATE RULE

CREATE SCHEMA

CREATE SERVER

CREATE SEQUENCE

CREATE TABLE

CREATE TABLE AS

CREATE TABLESPACE

CREATE TRIGGER

CREATE TYPE

CREATE USER

CREATE VIEW

DEALLOCATE

DECLARE

DELETE

DROP AGGREGATE

DROP CAST

DROP CONVERSION

DROP DATABASE

DROP DOMAIN

DROP FUNCTION

DROP GROUP

DROP INDEX

DROP LANGUAGE

DROP OPERATOR

DROP OPERATOR CLASS

DROP ROLE

DROP RULE

DROP SCHEMA

DROP SEQUENCE

DROP TABLE

DROP TABLESPACE

DROP TRIGGER

DROP TYPE