Postgresql 简明教程

PostgreSQL - Syntax

本章提供了 PostgreSQL SQL 命令的列表，后跟每个命令的精确语法规则。这组命令来自 psql 命令行工具。既然您已安装 Postgres，请以以下方式打开 psql：

This chapter provides a list of the PostgreSQL SQL commands, followed by the precise syntax rules for each of these commands. This set of commands is taken from the psql command-line tool. Now that you have Postgres installed, open the psql as −

Program Files → PostgreSQL 9.2 → SQL Shell(psql).

Program Files → PostgreSQL 9.2 → SQL Shell(psql).

使用 psql，您可以通过使用 \help 命令生成命令的完整列表。对于特定命令的语法，使用以下命令：

Using psql, you can generate a complete list of commands by using the \help command. For the syntax of a specific command, use the following command −

 postgres-# \help <command_name>

The SQL Statement

SQL 语句由标记组成，其中每个标记可以表示关键字、标识符、带引号的标识符、常量或特殊字符符号。下表使用一个简单的 SELECT 语句来说明一个基本但完整的 SQL 语句及其组成部分。

An SQL statement is comprised of tokens where each token can represent either a keyword, identifier, quoted identifier, constant, or special character symbol. The table given below uses a simple SELECT statement to illustrate a basic, but complete, SQL statement and its components.

SELECT

id, name

FROM

states

Token Type

Keyword

Identifiers

Keyword

Identifier

Description

Command

Id and name columns

Clause

Table name

PostgreSQL SQL commands

ABORT

中断当前事务。

Abort the current transaction.

ABORT [ WORK | TRANSACTION ]

ALTER AGGREGATE

更改聚合函数的定义。

Change the definition of an aggregate function.

ALTER AGGREGATE name ( type ) RENAME TO new_name
ALTER AGGREGATE name ( type ) OWNER TO new_owner

ALTER CONVERSION

更改转换的定义。

Change the definition of a conversion.

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

ALTER DATABASE

更改特定于数据库的参数。

Change a database specific parameter.

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

ALTER DOMAIN

更改特定于域的参数的定义。

Change the definition of a domain specific parameter.

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

ALTER FUNCTION

更改函数的定义。

Change the definition of a function.

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

ALTER GROUP

更改用户组。

Change a user group.

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

ALTER INDEX

更改索引的定义。

Change the definition of an index.

ALTER INDEX name OWNER TO new_owner
ALTER INDEX name SET TABLESPACE indexspace_name
ALTER INDEX name RENAME TO new_name

ALTER LANGUAGE

更改过程语言的定义。

Change the definition of a procedural language.

ALTER LANGUAGE name RENAME TO new_name

ALTER OPERATOR

更改运算符的定义。

Change the definition of an operator.

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

ALTER OPERATOR CLASS

更改操作员类的定义。

Change the definition of an operator class.

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

ALTER SCHEMA

更改模式的定义。

Change the definition of a schema.

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

ALTER SEQUENCE

更改序列发生器的定义。

Change the definition of a sequence generator.

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

ALTER TABLE

更改表定义。

Change the definition of a table.

ALTER TABLE [ ONLY ] name [ * ]
action [, ... ]
ALTER TABLE [ ONLY ] name [ * ]
RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
RENAME TO new_name

其中操作是以下行之一 −

Where action is one of the following lines −

ADD [ COLUMN ] column_type [ column_constraint [ ... ] ]
DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column TYPE type [ USING expression ]
ALTER [ COLUMN ] column SET DEFAULT expression
ALTER [ COLUMN ] column DROP DEFAULT
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
ALTER [ COLUMN ] column SET STATISTICS integer
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD table_constraint
DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITHOUT OIDS
OWNER TO new_owner
SET TABLESPACE tablespace_name

ALTER TABLESPACE

更改表空间的定义。

Change the definition of a tablespace.

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

ALTER TRIGGER

更改触发器的定义。

Change the definition of a trigger.

ALTER TRIGGER name ON table RENAME TO new_name

ALTER TYPE

更改类型的定义。

Change the definition of a type.

ALTER TYPE name OWNER TO new_owner

ALTER USER

更改数据库用户帐户。

Change a database user account.

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

其中选项可以为 −

Where option can be −

[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| VALID UNTIL 'abstime'

ANALYZE

收集有关数据库的统计信息。

Collect statistics about a database.

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

BEGIN

启动事务块。

Start a transaction block.

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

其中事务模式是 − 中的一个

Where transaction_mode is one of −

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

CHECKPOINT

强制事务日志检查点。

Force a transaction log checkpoint.

CHECKPOINT

CLOSE

关闭游标。

Close a cursor.

CLOSE name

CLUSTER

根据索引对表进行群集。

Cluster a table according to an index.

CLUSTER index_name ON table_name
CLUSTER table_name
CLUSTER

COMMENT

定义或更改对象的注释。

Define or change the comment of an object.

COMMENT ON {
   TABLE object_name |
   COLUMN table_name.column_name |
   AGGREGATE agg_name (agg_type) |
   CAST (source_type AS target_type) |
   CONSTRAINT constraint_name ON table_name |
   CONVERSION object_name |
   DATABASE object_name |
   DOMAIN object_name |
   FUNCTION func_name (arg1_type, arg2_type, ...) |
   INDEX object_name |
   LARGE OBJECT large_object_oid |
   OPERATOR op (left_operand_type, right_operand_type) |
   OPERATOR CLASS object_name USING index_method |
   [ PROCEDURAL ] LANGUAGE object_name |
   RULE rule_name ON table_name |
   SCHEMA object_name |
   SEQUENCE object_name |
   TRIGGER trigger_name ON table_name |
   TYPE object_name |
   VIEW object_name
}
IS 'text'

COMMIT

提交当前事务。

Commit the current transaction.

COMMIT [ WORK | TRANSACTION ]

COPY

在文件和表之间复制数据。

Copy data between a file and a table.

COPY table_name [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE NOT NULL column [, ...] ]
COPY table_name [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE QUOTE column [, ...] ]

CREATE AGGREGATE

定义一个新的聚合函数。

Define a new aggregate function.

CREATE AGGREGATE name (
   BASETYPE = input_data_type,
   SFUNC = sfunc,
   STYPE = state_data_type
   [, FINALFUNC = ffunc ]
   [, INITCOND = initial_condition ]
)

CREATE CAST

定义一个新的类型转换。

Define a new cast.

CREATE CAST (source_type AS target_type)
WITH FUNCTION func_name (arg_types)
[ AS ASSIGNMENT | AS IMPLICIT ]
CREATE CAST (source_type AS target_type)
WITHOUT FUNCTION
[ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CONSTRAINT TRIGGER

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

Define a new constraint trigger.

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

CREATE CONVERSION

定义一个新的转换。

Define a new conversion.

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

CREATE DATABASE

创建一个新数据库。

Create a new database.

CREATE DATABASE name
[ [ WITH ] [ OWNER [=] db_owner ]
   [ TEMPLATE [=] template ]
   [ ENCODING [=] encoding ]
   [ TABLESPACE [=] tablespace ]
]

CREATE DOMAIN

定义一个新域。

Define a new domain.

CREATE DOMAIN name [AS] data_type
[ DEFAULT expression ]
[ constraint [ ... ] ]

其中约束为-

Where constraint is −

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

CREATE FUNCTION

定义一个新函数。

Define a new function.

CREATE [ OR REPLACE ] FUNCTION name ( [ [ arg_name ] arg_type [, ...] ] )
RETURNS ret_type
{ LANGUAGE lang_name
   | IMMUTABLE | STABLE | VOLATILE
   | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
   | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
   | AS 'definition'
   | AS 'obj_file', 'link_symbol'
} ...
[ WITH ( attribute [, ...] ) ]

CREATE GROUP

定义一个新用户组。

Define a new user group.

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

CREATE INDEX

定义一个新索引。

Define a new index.

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

CREATE LANGUAGE

定义一个新的过程语言。

Define a new procedural language.

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

CREATE OPERATOR

定义一个新运算符。

Define a new operator.

CREATE OPERATOR name (
   PROCEDURE = func_name
   [, LEFTARG = left_type ] [, RIGHTARG = right_type ]
   [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
   [, RESTRICT = res_proc ] [, JOIN = join_proc ]
   [, HASHES ] [, MERGES ]
   [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ]
   [, LTCMP = less_than_op ] [, GTCMP = greater_than_op ]
)

CREATE OPERATOR CLASS

定义一个新的运算符类。

Define a new operator class.

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

CREATE RULE

定义一个新的重写规则。

Define a new rewrite rule.

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

CREATE SCHEMA

定义一个新模式。

Define a new schema.

CREATE SCHEMA schema_name
[ AUTHORIZATION username ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION username
[ schema_element [ ... ] ]

CREATE SEQUENCE

定义一个新序列发生器。

Define a new sequence generator.

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

CREATE TABLE

定义一个新表。

Define a new table.

CREATE [ [ GLOBAL | LOCAL ] {
   TEMPORARY | TEMP } ] TABLE table_name ( {
      column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
      | table_constraint
      | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ]
   } [, ... ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]

其中 column_constraint 为-

Where column_constraint is −

[ CONSTRAINT constraint_name ] {
   NOT NULL |
   NULL |
   UNIQUE [ USING INDEX TABLESPACE tablespace ] |
   PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
   CHECK (expression) |
   REFERENCES ref_table [ ( ref_column ) ]
   [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
   [ ON DELETE action ] [ ON UPDATE action ]
}
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

其中 table_constraint 为-

And table_constraint is −

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

CREATE TABLE AS

根据查询结果定义一个新表。

Define a new table from the results of a query.

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

CREATE TABLESPACE

定义一个新的表空间。

Define a new tablespace.

CREATE TABLESPACE tablespace_name [ OWNER username ] LOCATION 'directory'

CREATE TRIGGER

定义一个新的触发器。

Define a new trigger.

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

CREATE TYPE

定义一个新的数据类型。

Define a new data type.

CREATE TYPE name AS
( attribute_name data_type [, ... ] )
CREATE TYPE name (
INPUT = input_function,
OUTPUT = output_function
[, RECEIVE = receive_function ]
[, SEND = send_function ]
[, ANALYZE = analyze_function ]
[, INTERNALLENGTH = { internal_length | VARIABLE } ]
[, PASSEDBYVALUE ]
[, ALIGNMENT = alignment ]
[, STORAGE = storage ]
[, DEFAULT = default ]
[, ELEMENT = element ]
[, DELIMITER = delimiter ]
)

CREATE USER

定义一个新的数据库用户帐户。

Define a new database user account.

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

其中选项可以为 −

Where option can be −

SYSID uid
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| IN GROUP group_name [, ...]
| VALID UNTIL 'abs_time'

CREATE VIEW

定义一个新的视图。

Define a new view.

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

DEALLOCATE

取消分配准备好的语句。

Deallocate a prepared statement.

DEALLOCATE [ PREPARE ] plan_name

DECLARE

定义一个游标。

Define a cursor.

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

DELETE

删除表中的行。

Delete rows of a table.

DELETE FROM [ ONLY ] table [ WHERE condition ]

DROP AGGREGATE

删除一个聚集函数。

Remove an aggregate function.

DROP AGGREGATE name ( type ) [ CASCADE | RESTRICT ]

DROP CAST

删除一个 cast。

Remove a cast.

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

DROP CONVERSION

删除一个转换。

Remove a conversion.

DROP CONVERSION name [ CASCADE | RESTRICT ]

DROP DATABASE

删除一个数据库。

Remove a database.

DROP DATABASE name

DROP DOMAIN

删除一个域。

Remove a domain.

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

DROP FUNCTION

删除一个函数。

Remove a function.

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

DROP GROUP

删除一个用户组。

Remove a user group.

DROP GROUP name

DROP INDEX

删除一个索引。

Remove an index.

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

DROP LANGUAGE

删除一个过程语言。

Remove a procedural language.

DROP [ PROCEDURAL ] LANGUAGE name [ CASCADE | RESTRICT ]

DROP OPERATOR

删除一个操作符。

Remove an operator.

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

DROP OPERATOR CLASS

删除操作员类。

Remove an operator class.

DROP OPERATOR CLASS name USING index_method [ CASCADE | RESTRICT ]

DROP RULE

删除重写规则。

Remove a rewrite rule.

DROP RULE name ON relation [ CASCADE | RESTRICT ]

DROP SCHEMA

删除模式。

Remove a schema.

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

DROP SEQUENCE

删除序列。

Remove a sequence.

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

DROP TABLE

删除表。

Remove a table.

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

DROP TABLESPACE

删除表空间。

Remove a tablespace.

DROP TABLESPACE tablespace_name

DROP TRIGGER

删除触发器。

Remove a trigger.

DROP TRIGGER name ON table [ CASCADE | RESTRICT ]

DROP TYPE

删除数据类型。

Remove a data type.

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

DROP USER

删除数据库用户帐户。

Remove a database user account.

DROP USER name

DROP VIEW

删除视图。

Remove a view.

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

END

提交当前事务。

Commit the current transaction.

END [ WORK | TRANSACTION ]

EXECUTE

执行已准备好的语句。

Execute a prepared statement.

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

EXPLAIN

显示语句的执行计划。

Show the execution plan of a statement.

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

FETCH

使用游标从查询中检索行。

Retrieve rows from a query using a cursor.

FETCH [ direction { FROM | IN } ] cursor_name

路径方向可以为空或如下内容之一 −

Where direction can be empty or one of −

NEXT
PRIOR
FIRST
LAST
ABSOLUTE count
RELATIVE count
count
ALL
FORWARD
FORWARD count
FORWARD ALL
BACKWARD
BACKWARD count
BACKWARD ALL

GRANT

定义访问权限。

Define access privileges.

GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION func_name ([type, ...]) [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

INSERT

在表中创建新行。

Create new rows in a table.

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

LISTEN

监听通知。

Listen for a notification.

LISTEN name

LOAD

载入或重新载入共享库文件。

Load or reload a shared library file.

LOAD 'filename'

LOCK

锁定表。

Lock a table.

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

其中 lock_mode 是 - 中的一个：

Where lock_mode is one of −

ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

MOVE

放置一个光标。

Position a cursor.

MOVE [ direction { FROM | IN } ] cursor_name

NOTIFY

生成一个通知。

Generate a notification.

NOTIFY name

PREPARE

准备一条语句用以执行。

Prepare a statement for execution.

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

REINDEX

重建索引。

Rebuild indexes.

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

RELEASE SAVEPOINT

销毁一个之前定义的保存点。

Destroy a previously defined savepoint.

RELEASE [ SAVEPOINT ] savepoint_name

RESET

将一个运行时参数的值还原为默认值。

Restore the value of a runtime parameter to the default value.

RESET name
RESET ALL

REVOKE

移除访问权限。

Remove access privileges.

REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION func_name ([type, ...]) [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

ROLLBACK

中断当前事务。

Abort the current transaction.

ROLLBACK [ WORK | TRANSACTION ]

ROLLBACK TO SAVEPOINT

回滚到一个保存点。

Roll back to a savepoint.

ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name

SAVEPOINT

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

Define a new savepoint within the current transaction.

SAVEPOINT savepoint_name

SELECT

从一个表或视图中检索行。

Retrieve rows from a table or view.

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]
[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
function_name ( [ argument [, ...] ] )
[ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
from_item [ NATURAL ] join_type from_item
[ ON join_condition | USING ( join_column [, ...] ) ]

SELECT INTO

根据查询结果定义一个新表。

Define a new table from the results of a query.

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]

SET

更改一个运行时参数。

Change a runtime parameter.

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

SET CONSTRAINTS

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

Set constraint checking modes for the current transaction.

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

SET SESSION AUTHORIZATION

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

Set the session user identifier and the current user identifier of the current session.

SET [ SESSION | LOCAL ] SESSION AUTHORIZATION username
SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT
RESET SESSION AUTHORIZATION

SET TRANSACTION

设置当前事务的特征。

Set the characteristics of the current transaction.

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

其中事务模式是 − 中的一个

Where transaction_mode is one of −

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

SHOW

显示一个运行时参数的值。

Show the value of a runtime parameter.

SHOW name
SHOW ALL

START TRANSACTION

启动事务块。

Start a transaction block.

START TRANSACTION [ transaction_mode [, ...] ]

其中事务模式是 − 中的一个

Where transaction_mode is one of −

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

TRUNCATE

清空一个表。

Empty a table.

TRUNCATE [ TABLE ] name

UNLISTEN

停止监听一个通知。

Stop listening for a notification.

UNLISTEN { name | * }

UPDATE

更新一个表中的行。

Update rows of a table.

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

VACUUM

手动收集垃圾，可选地分析数据库。

Garbage-collect and optionally analyze a database.

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