AntDB分布式关系型数据库_DDl语句

CREATE DATABASE

说明：

创建一个新数据库。

语法：

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LOCALE [=] locale ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ] 
           [ IS_TEMPLATE [=] istemplate ] ]

示例：

-- 创建一个新数据库：
CREATE DATABASE lusiadas;

--在一个默认表空间salesspace中创建一个被用户salesapp拥有的新数据库sales：
CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;

ALTER DATABASE

说明：

更改一个数据库

语法：

ALTER DATABASE name [ [ WITH ] option [ ... ] ]

这里 option 可以是：

    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT connlimit
    IS_TEMPLATE istemplate

ALTER DATABASE name RENAME TO new_name

ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

ALTER DATABASE name SET TABLESPACE new_tablespace

ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

示例：

--要在数据库test中默认禁用索引扫描：
ALTER DATABASE test SET enable_indexscan TO off;

DROP DATABASE

说明：

移除一个数据库

语法：

DROP DATABASE [ IF EXISTS ] name [ [ WITH ] ( option [, ...] ) ]

其中选项 可以是:

    FORCE

示例：

--移除数据库adb01，若adb01不存在，会报错。
DROP DATABASE adb01;

--移除数据库adb01，即使adb01不存在，也不会报错。
DROP DATABASE IF EXISTS adb01;

CREATE TABLE

说明：

创建数据库表格。表名必需在同一模式中的其它表、序列、索引、视图或外部表名字中唯一。

语法：

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    OF type_name [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    PARTITION OF parent_table [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ] { FOR VALUES partition_bound_spec | DEFAULT }
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

示例：

以下创建了一张表名为COMPANY表格，主键为ID，NOT NULL表示字段不允许包含NULL值：

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

ALTER TABLE

说明：

更改一个表的定义

语法：

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
    RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
    SET SCHEMA new_schema
ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]
ALTER TABLE [ IF EXISTS ] name
    ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
ALTER TABLE [ IF EXISTS ] name
    DETACH PARTITION partition_name

其中常用action可以是以下之一：

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 TABLE distributors RENAME TO suppliers;

--为一列增加一个非空约束：
ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

--把一个表移动到一个不同的模式：
ALTER TABLE myschema.distributors SET SCHEMA yourschema;

DROP TABLE

说明：

移除一个表

语法：

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

示例：

--移除department和company两张表，若表格不存在，会报错
DROP TABLE department, company;

--移除department表，即使表格不存在，也会报错
DROP TABLE IF EXISTS department;

CREATE VIEW

说明：

定义一个新视图

语法：

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

示例：

--创建一个基于表格COMPANY的视图COMPANY_VIEW
CREATE VIEW COMPANY_VIEW AS SELECT ID, NAME, AGE FROM COMPANY;

ALTER VIEW

说明：

更改一个视图的定义

语法：

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER VIEW [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER VIEW [ IF EXISTS ] name RENAME TO new_name
ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema
ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )
ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )

示例：

--把视图foo重命名为bar：
ALTER VIEW foo RENAME TO bar;

--为视图a_view的列ts附加一个默认列值
ALTER VIEW a_view ALTER COLUMN ts SET DEFAULT now();

DROP VIEW

说明：

移除一个视图

语法：

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

示例：

--移除视图COMPANY_VIEW，若视图不存在会报错
DROP VIEW COMPANY_VIEW;

--移除视图COMPANY_VIEW，即使视图不存在，也不会报错
DROP VIEW IF EXISTS COMPANY_VIEW;

CREATE INDEX

说明：

定义一个新索引

语法：

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

示例：

--在表films中的列title上创建一个 B-树索引：
CREATE UNIQUE INDEX title_idx ON films (title);

ALTER INDEX

说明：

更改一个索引的定义

语法：

ALTER INDEX [ IF EXISTS ] name RENAME TO new_name
ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name
ALTER INDEX name ATTACH PARTITION index_name
ALTER INDEX name DEPENDS ON EXTENSION extension_name
ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter [= value] [, ... ] )
ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] )
ALTER INDEX [ IF EXISTS ] name ALTER [ COLUMN ] column_number
    SET STATISTICS integer
ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]

示例：

--将索引distributors重命名为suppliers：
ALTER INDEX distributors RENAME TO suppliers;

--把索引distributors移动到表空间fasttablespace中：
ALTER INDEX distributors SET TABLESPACE fasttablespace;

DROP INDEX

说明：

移除一个索引

语法：

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

示例：

--移除索引title_idx：
DROP INDEX title_idx;

CREATE FUNCTION

说明：

定义一个新函数

语法：

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SUPPORT support_function
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

示例：

--创建一个简单加法运算函数：
CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

ALTER FUNCTION

说明：

更改一个函数的定义

语法：

ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
    action [ ... ] [ RESTRICT ]
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
    RENAME TO new_name
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
    OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
    SET SCHEMA new_schema
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
    [ NO ] DEPENDS ON EXTENSION extension_name

其中 action 是以下之一：

    CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
    [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    PARALLEL { UNSAFE | RESTRICTED | SAFE }
    COST execution_cost
    ROWS result_rows
    SUPPORT support_function
    SET configuration_parameter { TO | = } { value | DEFAULT }
    SET configuration_parameter FROM CURRENT
    RESET configuration_parameter
    RESET ALL

示例：

--把用于类型integer的函数sqrt 重命名为square_root：
ALTER FUNCTION sqrt(integer) RENAME TO square_root;

--要把用于类型integer的函数sqrt 的模式改为maths：
ALTER FUNCTION sqrt(integer) SET SCHEMA maths;

DROP FUNCTION

说明：

移除一个函数

语法：

DROP FUNCTION [ IF EXISTS ] name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] [, ...]
    [ CASCADE | RESTRICT ]

示例：

--移除sqrt对应的两个函数
DROP FUNCTION sqrt(integer), sqrt(bigint);

CREATE PROCEDURE

说明：

定义一个新的过程

语法：

CREATE [ OR REPLACE ] PROCEDURE
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

示例：

--创建一个插入数据的过程，用CALL调用：
CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
AS $$
INSERT INTO tbl VALUES (a);
INSERT INTO tbl VALUES (b);
$$;

CALL insert_data(1, 2);

ALTER PROCEDURE

说明：

更改一个过程的定义

语法：

ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
    action [ ... ] [ RESTRICT ]
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
    RENAME TO new_name
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
    OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
    SET SCHEMA new_schema
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
    DEPENDS ON EXTENSION extension_name

其中action是下列之一：

    [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    SET configuration_parameter { TO | = } { value | DEFAULT }
    SET configuration_parameter FROM CURRENT
    RESET configuration_parameter
    RESET ALL

示例：

--重命名具有两个integer类型参数的过程insert_data为insert_record：
ALTER PROCEDURE insert_data(integer, integer) RENAME TO insert_record;

--要把具有两个integer类型参数的过程insert_data的拥有者改为joe：
ALTER PROCEDURE insert_data(integer, integer) OWNER TO joe;

DROP PROCEDURE

说明：

移除一个过程

语法：

DROP PROCEDURE [ IF EXISTS ] name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] [, ...]
    [ CASCADE | RESTRICT ]

示例：

--移除过程insert_data：
DROP PROCEDURE insert_data();

CREATE SCHEMA

说明：

定义一个新模式

语法：

CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

其中 role_specification 可以是：

    user_name
  | CURRENT_USER
  | SESSION_USER

示例：

--创建一个模式：
CREATE SCHEMA myschema;

--为用户joe创建一个模式，该模式也将被命名为 joe：
CREATE SCHEMA AUTHORIZATION joe;

ALTER SCHEMA

说明：

更改一个模式的定义

语法：

ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

示例：

--将模式sch_a重命名为sch_b：
ALTER SCHEMA sch_a RENAME TO sch_b;

DROP SCHEMA

说明：

移除一个模式

语法：

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

示例：

--从数据库中移除模式mystuff及其中 所包含的对象：
DROP SCHEMA mystuff CASCADE;

CREATE USER

说明：

定义一个新的数据库角色

语法：

CREATE USER 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

示例：

--创建一个超级用户，并赋予登录权限
CREATE USER user1 SUPERUSER PASSWORD '123456' LOGIN;

ALTER USER

说明：

更改一个数据库角色

语法：

ALTER USER role_specification [ 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'

ALTER USER name RENAME TO new_name

ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL

其中 role_specification 可以是：

    role_name
  | CURRENT_USER
  | SESSION_USER

示例：

--重命名用户
ALTER USER adb01 RENAME TO adb02;

DROP USER

说明：

移除一个数据库角色

语法：

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

示例：

--移除用户adb01:
DROP USER IF EXISTS adb01;

CREATE ROLE

说明：

定义一个新的数据库角色

语法：

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

where 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

示例：

--创建一个能登录但是没有口令的角色：
CREATE ROLE jonathan LOGIN;

--创建一个有口令的角色：
CREATE USER davide WITH PASSWORD 'jw8s0F4';

ALTER ROLE

说明：

更改一个数据库角色

语法：

ALTER ROLE role_specification [ 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'

ALTER ROLE name RENAME TO new_name

ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL

其中role_specification可以是：

    role_name
  | CURRENT_USER
  | SESSION_USER

示例：

--更改一个角色的口令：
ALTER ROLE davide WITH PASSWORD 'hu8jmn3';

--让一个角色能够创建其他角色和新的数据库：
ALTER ROLE miriam CREATEROLE CREATEDB;

DROP ROLE

说明：

移除一个数据库角色

语法：

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

示例：

--删除一个角色：
DROP ROLE davide;

COMMENT

说明：

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

语法：

COMMENT ON
{
  ACCESS METHOD object_name |
  AGGREGATE aggregate_name ( aggregate_signature ) |
  CAST (source_type AS target_type) |
  COLLATION object_name |
  COLUMN relation_name.column_name |
  CONSTRAINT constraint_name ON table_name |
  CONSTRAINT constraint_name ON DOMAIN domain_name |
  CONVERSION object_name |
  DATABASE object_name |
  DOMAIN object_name |
  EXTENSION object_name |
  EVENT TRIGGER object_name |
  FOREIGN DATA WRAPPER object_name |
  FOREIGN TABLE object_name |
  FUNCTION function_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
  INDEX object_name |
  LARGE OBJECT large_object_oid |
  MATERIALIZED VIEW object_name |
  OPERATOR operator_name (left_type, right_type) |
  OPERATOR CLASS object_name USING index_method |
  OPERATOR FAMILY object_name USING index_method |
  POLICY policy_name ON table_name |
  [ PROCEDURAL ] LANGUAGE object_name |
  PROCEDURE procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
  PUBLICATION object_name |
  ROLE object_name |
  ROUTINE routine_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
  RULE rule_name ON table_name |
  SCHEMA object_name |
  SEQUENCE object_name |
  SERVER object_name |
  STATISTICS object_name |
  SUBSCRIPTION object_name |
  TABLE object_name |
  TABLESPACE object_name |
  TEXT SEARCH CONFIGURATION object_name |
  TEXT SEARCH DICTIONARY object_name |
  TEXT SEARCH PARSER object_name |
  TEXT SEARCH TEMPLATE object_name |
  TRANSFORM FOR type_name LANGUAGE lang_name |
  TRIGGER trigger_name ON table_name |
  TYPE object_name |
  VIEW object_name
} IS 'text'

其中 aggregate_signature 是：

* |
[ argmode ] [ argname ] argtype [ , ... ] |
[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]

示例：

--为表mytable附加一段注释：
COMMENT ON TABLE mytable IS 'This is my table.';

TRUNCATE

说明：

清空一个表或者一组表

语法：

TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
    [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]

示例：

--清空表bigtable和 fattable：
TRUNCATE bigtable, fattable;

DDL语句

CREATE DATABASE

ALTER DATABASE

DROP DATABASE

CREATE TABLE

ALTER TABLE

DROP TABLE

CREATE VIEW

ALTER VIEW

DROP VIEW

CREATE INDEX

ALTER INDEX

DROP INDEX

CREATE FUNCTION

ALTER FUNCTION

DROP FUNCTION

CREATE PROCEDURE

ALTER PROCEDURE

DROP PROCEDURE

CREATE SCHEMA

ALTER SCHEMA

DROP SCHEMA

CREATE USER

ALTER USER

DROP USER

CREATE ROLE

ALTER ROLE

DROP ROLE

COMMENT

TRUNCATE