ADB for PostgreSQL支持的语法

目录

• CREATE INDEX

• CREATE ROLE

• CREATE SEQUENCE

• CREATE USER

• CREATE VIEW

• CREATE SCHEMA

• CREATE TABLE

• CREATE TABLE AS

• SELECT

• ALTER DATABASE

• ALTER INDEX

• ALTER SCHEMA

• ALTER TABLE

• DROP INDEX

• DROP SCHEMA

• DROP ROLE

• DROP SEQUENCE

• DROP USER

• DROP VIEW

• DROP TABLE

• BEGIN

• CHECKPOINT

• CLOSE

• CLUSTER

• COMMENT

• COMMIT

• DEALLOCATE

• DECLARE

• DELETE

• END

• EXPLAIN

• FETCH

• LOAD

• LOCK

• MOVE

• PREPARE

• REASSIGN OWNED

• REINDEX

• RELEASE SAVEPOINT

• ROLLBACK

• ROLLBACK TO SAVEPOINT

• SAVEPOINT

• SELECT INTO

• SET

• SET ROLE

• SET SESSION AUTHORIZATION

• SET TRANSACTION

• SHOW

• START TRANSACTION

• TRUNCATE

• UPDATE

• VACUUM

• VALUES

CREATE INDEX

定义索引。

CREATE [UNIQUE] INDEX name ON table
       [USING btree|bitmap|gist]
       ( {column | (expression)} [opclass] [, ...] )
       [ WITH ( FILLFACTOR = value ) ]
       [TABLESPACE tablespace]
       [WHERE predicate]

更多信息请参见CREATE INDEX。

CREATE ROLE

定义数据库角色（用户或组）。

CREATE ROLE name [[WITH] option [ ... ]]

更多信息请参见CREATE ROLE。

CREATE SEQUENCE

定义序列生成器。

CREATE [TEMPORARY | TEMP] SEQUENCE name
       [INCREMENT [BY] value] 
       [MINVALUE minvalue | NO MINVALUE] 
       [MAXVALUE maxvalue | NO MAXVALUE] 
       [START [ WITH ] start] 
       [CACHE cache] 
       [[NO] CYCLE] 
       [OWNED BY { table.column | NONE }]

更多信息请参见CREATE SEQUENCE。

CREATE USER

定义默认具有LOGIN权限的数据库角色。

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

更多信息请参见CREATE USER。

CREATE VIEW

定义视图。

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

更多信息请参见CREATE VIEW。

CREATE SCHEMA

定义SCHEMA。

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

CREATE SCHEMA AUTHORIZATION rolename [schema_element [ ... ]]

更多信息请参见CREATE SCHEMA。

CREATE TABLE

定义表。

CREATE [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name ( 
[ { column_name data_type [ DEFAULT default_expr ] 
   [column_constraint [ ... ]
[ ENCODING ( storage_directive [,...] ) ]
] 
   | table_constraint
   | LIKE other_table [{INCLUDING | EXCLUDING} 
                      {DEFAULTS | CONSTRAINTS}] ...}
   [, ... ] ]
   )
   [ INHERITS ( parent_table [, ... ] ) ]
   [ WITH ( storage_parameter=value [, ... ] )
   [ ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP} ]
   [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
   [ PARTITION BY partition_type (column)
       [ SUBPARTITION BY partition_type (column) ] 
          [ SUBPARTITION TEMPLATE ( template_spec ) ]
       [...]
    ( partition_spec ) 
        | [ SUBPARTITION BY partition_type (column) ]
          [...]
    ( partition_spec
      [ ( subpartition_spec
           [(...)] 
         ) ] 
    )

更多信息请参见CREATE TABLE。

CREATE TABLE AS

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

CREATE [ [GLOBAL | LOCAL] {TEMPORARY | TEMP} ] TABLE table_name
   [(column_name [, ...] )]
   [ WITH ( storage_parameter=value [, ... ] ) ]
   [ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP}]
   [TABLESPACE tablespace]
   AS query
   [DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY]

更多信息请参见CREATE TABLE AS。

SELECT

从表或视图中检索行。

[ WITH [ RECURSIVE ] <with_query> [, ...] ]
SELECT [ALL | DISTINCT [ON (<expression> [, ...])]]
  * | <expression >[[AS] <output_name>] [, ...]
  [FROM <from_item> [, ...]]
  [WHERE <condition>]
  [GROUP BY <grouping_element> [, ...]]
  [HAVING <condition> [, ...]]
  [WINDOW <window_name> AS (<window_definition>) [, ...] ]
  [{UNION | INTERSECT | EXCEPT} [ALL | DISTINCT] <select>]
  [ORDER BY <expression> [ASC | DESC | USING <operator>] [NULLS {FIRST | LAST}] [, ...]]
  [LIMIT {<count> | ALL}]
  [OFFSET <start> [ ROW | ROWS ] ]
  [FETCH { FIRST | NEXT } [ <count> ] { ROW | ROWS } ONLY]
  [FOR {UPDATE | NO KEY UPDATE | SHARE | KEY SHARE} [OF <table_name> [, ...]] [NOWAIT] [...]]

更多信息请参见SELECT。

ALTER DATABASE

修改数据库属性。

ALTER DATABASE name [ WITH CONNECTION LIMIT connlimit ]
ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name RESET parameter
ALTER DATABASE name RENAME TO newname
ALTER DATABASE name OWNER TO new_owner

更多信息请参见ALTER DATABASE。

ALTER INDEX

改变索引的定义。

ALTER INDEX name RENAME TO new_name
ALTER INDEX name SET TABLESPACE tablespace_name
ALTER INDEX name SET ( FILLFACTOR = value )
ALTER INDEX name RESET ( FILLFACTOR )

更多信息请参见ALTER INDEX。

ALTER SCHEMA

改变模式的定义。

ALTER SCHEMA name RENAME TO newname

ALTER SCHEMA name OWNER TO newowner

更多信息请参见ALTER SCHEMA。

ALTER TABLE

改变的表的定义。

ALTER TABLE [ONLY] name RENAME [COLUMN] column TO new_column

ALTER TABLE name RENAME TO new_name

ALTER TABLE name SET SCHEMA new_schema

ALTER TABLE [ONLY] name SET 
     DISTRIBUTED BY (column, [ ... ] ) 
   | DISTRIBUTED RANDOMLY 
   | WITH (REORGANIZE=true|false)

ALTER TABLE [ONLY] name action [, ... ]

ALTER TABLE name
   [ ALTER PARTITION { partition_name | FOR (RANK(number)) 
   | FOR (value) } partition_action [...] ] 
   partition_action

更多信息请参见ALTER TABLE。

DROP INDEX

删除索引。

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

更多信息请参见DROP INDEX。

DROP SCHEMA

删除SCHEMA。

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

更多信息请参见DROP SCHEMA。

DROP ROLE

删除数据库角色。

DROP ROLE [IF EXISTS] name [, ...]

更多信息请参见DROP ROLE。

DROP SEQUENCE

删除序列。

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

更多信息请参见DROP SEQUENCE。

DROP USER

删除数据库角色。

DROP USER [IF EXISTS] name [, ...]

更多信息请参见DROP USER。

DROP VIEW

删除视图。

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

更多信息请参见DROP VIEW。

DROP TABLE

删除表。

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

更多信息请参见DROP TABLE。

BEGIN

启动事务块。

BEGIN [WORK | TRANSACTION] [transaction_mode]
      [READ ONLY | READ WRITE]

更多信息请参见BEGIN 。

CHECKPOINT

强制事务记录检查点。

CHECKPOINT

更多信息请参见CHECKPOINT。

CLOSE

关闭游标。

CLOSE cursor_name

更多信息请参见CLOSE 。

CLUSTER

根据索引对磁盘上的堆存储表，重新排序。

CLUSTER indexname ON tablename

CLUSTER tablename

CLUSTER

更多信息请参见CLUSTER。

COMMENT

定义或修改对象的注释。

COMMENT ON
{ TABLE object_name |
  COLUMN table_name.column_name |
  AGGREGATE agg_name (agg_type [, ...]) |
  CAST (sourcetype AS targettype) |
  CONSTRAINT constraint_name ON table_name |
  CONVERSION object_name |
  DATABASE object_name |
  DOMAIN object_name |
  FILESPACE object_name |
  FUNCTION func_name ([[argmode] [argname] argtype [, ...]]) |
  INDEX object_name |
  LARGE OBJECT large_object_oid |
  OPERATOR op (leftoperand_type, rightoperand_type) |
  OPERATOR CLASS object_name USING index_method |
  [PROCEDURAL] LANGUAGE object_name |
  RESOURCE QUEUE object_name |
  ROLE object_name |
  RULE rule_name ON table_name |
  SCHEMA object_name |
  SEQUENCE object_name |
  TABLESPACE object_name |
  TRIGGER trigger_name ON table_name |
  TYPE object_name |
  VIEW object_name } 
IS 'text'

更多信息请参见COMMENT。

COMMIT

提交当前事务。

COMMIT [WORK | TRANSACTION]

更多信息请参见COMMIT。

DEALLOCATE

取消分配预编译的语句。

DEALLOCATE [PREPARE] name

更多信息请参见DEALLOCATE。

DECLARE

定义游标。

DECLARE name [BINARY] [INSENSITIVE] [NO SCROLL] CURSOR 
     [{WITH | WITHOUT} HOLD] 
     FOR query [FOR READ ONLY]

更多信息请参见DECLARE。

DELETE

从表中删除行。

DELETE FROM [ONLY] table [[AS] alias]
      [USING usinglist]
      [WHERE condition | WHERE CURRENT OF cursor_name ]

更多信息请参见DELETE。

END

提交当前事务。

END [WORK | TRANSACTION]

更多信息请参见END。

EXPLAIN

展示语句的查询计划。

EXPLAIN [ANALYZE] [VERBOSE] statement

更多信息请参见EXPLAIN。

FETCH

通过游标获取查询结果的行。

FETCH [ forward_direction { FROM | IN } ] cursorname

更多信息请参见FETCH 。

LOAD

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

LOAD 'filename'

更多信息请参见 LOAD。

LOCK

锁住一张表。

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

更多信息请参见LOCK。

MOVE

放置游标。

MOVE [ forward_direction {FROM | IN} ] cursorname

更多信息请参见MOVE。

PREPARE

准备一个执行的语句。

PREPARE name [ (datatype [, ...] ) ] AS statement

更多信息请参见PREPARE。

REASSIGN OWNED

修改数据库角色。

REASSIGN OWNED BY old_role [, ...] TO new_role

更多信息请参见REASSIGN OWNED。

REINDEX

重新构建索引。

REINDEX {INDEX | TABLE | DATABASE | SYSTEM} name

更多信息请参见REINDEX。

RELEASE SAVEPOINT

销毁定义过的SAVEPOINT。

RELEASE [SAVEPOINT] savepoint_name

更多信息请参见RELEASE SAVEPOINT。

ROLLBACK

中止当前事务。

ROLLBACK [WORK | TRANSACTION]

更多信息请参见ROLLBACK。

ROLLBACK TO SAVEPOINT

将当前事务回滚到某个SAVEPOINT。

ROLLBACK [WORK | TRANSACTION] TO [SAVEPOINT] savepoint_name

更多信息请参见ROLLBACK TO SAVEPOINT。

SAVEPOINT

在当前事务定义一个新的SAVEPOINT。

SAVEPOINT savepoint_name

更多信息请参见SAVEPOINT。

SELECT INTO

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

[ WITH with_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] [NULLS {FIRST | LAST}] [, ...]]
    [LIMIT {count | ALL}]
    [OFFSET start]
    [FOR {UPDATE | SHARE} [OF table_name [, ...]] [NOWAIT] 
    [...]]

更多信息请参见SELECT INTO。

SET

改变数据库配置参数的值。

SET [SESSION | LOCAL] configuration_parameter {TO | =} value | 
    'value' | DEFAULT}

SET [SESSION | LOCAL] TIME ZONE {timezone | LOCAL | DEFAULT}

更多信息请参见SET。

SET ROLE

设置当前会话当前角色的标识符。

SET [SESSION | LOCAL] ROLE rolename

SET [SESSION | LOCAL] ROLE NONE

RESET ROLE

更多信息请参见SET ROLE。

SET SESSION AUTHORIZATION

设置会话角色标识符和当前会话当前角色的标识符。

SET [SESSION | LOCAL] SESSION AUTHORIZATION rolename

SET [SESSION | LOCAL] SESSION AUTHORIZATION DEFAULT

RESET SESSION AUTHORIZATION

更多信息请参见SET SESSION AUTHORIZATION。

SET TRANSACTION

设置当前事务的特征。

SET TRANSACTION [transaction_mode] [READ ONLY | READ WRITE]

SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode 
     [READ ONLY | READ WRITE]

更多信息请参见SET TRANSACTION。

SHOW

显示当前系统配置参数的值。

SHOW configuration_parameter

SHOW ALL

更多信息请参见SHOW。

START TRANSACTION

开始事务块。

START TRANSACTION [SERIALIZABLE | READ COMMITTED | READ UNCOMMITTED]
                  [READ WRITE | READ ONLY]

更多信息请参见START TRANSACTION。

TRUNCATE

清空表的所有行。

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

更多信息请参见TRUNCATE。

UPDATE

更新表的行。

UPDATE [ONLY] table [[AS] alias]
   SET {column = {expression | DEFAULT} |
   (column [, ...]) = ({expression | DEFAULT} [, ...])} [, ...]
   [FROM fromlist]
   [WHERE condition | WHERE CURRENT OF cursor_name ]

更多信息请参见UPDATE。

VACUUM

垃圾回收。

VACUUM [({ FULL | FREEZE | VERBOSE | ANALYZE | DISABLE_PAGE_SKIPPING | SKIP_LOCKED | INDEX_CLEANUP } [, ...])] 
[<table> [(<column> [, ...] )]]

更多信息请参见VACUUM。

VALUES

计算一组行。

VALUES ( expression [, ...] ) [, ...]
   [ORDER BY sort_expression [ASC | DESC | USING operator] [, ...]]
   [LIMIT {count | ALL}] [OFFSET start]

更多信息请参见VALUES。