附录:SQL语法

ABORT

退出当前事务。

  1. ABORT [ WORK | TRANSACTION ] ;

ALTER AUDIT POLICY

修改统一审计策略。

  1. ALTER AUDIT POLICY [ IF EXISTS ] policy_name { ADD | REMOVE } { [ privilege_audit_clause ] [ access_audit_clause ] };
  2. ALTER AUDIT POLICY [ IF EXISTS ] policy_name MODIFY ( filter_group_clause );
  3. ALTER AUDIT POLICY [ IF EXISTS ] policy_name DROP FILTER;
  4. ALTER AUDIT POLICY [ IF EXISTS ] policy_name COMMENTS policy_comments;
  5. ALTER AUDIT POLICY [ IF EXISTS ] policy_name { ENABLE | DISABLE };
  6. where privilege_audit_clause can be:
  7. PRIVILEGES { DDL | ALL }
  8. where access_audit_clause can be:
  9. ACCESS { DML | ALL }
  10. where filter_group_clause can be:
  11. FILTER ON { ( FILTER_TYPE ( filter_value [, ... ] ) ) [, ... ] }
  12. where DDL can be:
  13. { ( ALTER | ANALYZE | COMMENT | CREATE | DROP | GRANT | REVOKE | SET | SHOW | LOGIN_ACCESS | LOGIN_FAILURE | LOGOUT | LOGIN ) }
  14. where DML can be:
  15. { ( COPY | DEALLOCATE | DELETE_P | EXECUTE | REINDEX | INSERT | REPARE | SELECT | TRUNCATE | UPDATE ) }

ALTER DATA SOURCE

修改Data Source对象的属性和内容。

  1. ALTER DATA SOURCE src_name
  2. [TYPE 'type_str']
  3. [VERSION {'version_str' | NULL}]
  4. [OPTIONS ( { [ADD | SET | DROP] optname ['optvalue'] } [, ...] )];
  5. ALTER DATA SOURCE src_name RENAME TO src_new_name;
  6. ALTER DATA SOURCE src_name OWNER TO new_owner;
  7. Valid optname are:
  8. DSN, USERNAME, PASSWORD, ENCODING

ALTER DATABASE

修改数据库的属性,包括它的名称、所有者、连接数限制、对象隔离属性等。

  1. ALTER DATABASE database_name
  2. [ [ WITH ] CONNECTION LIMIT connlimit ];
  3. ALTER DATABASE database_name
  4. RENAME TO new_name;
  5. ALTER DATABASE database_name
  6. OWNER TO new_owner;
  7. ALTER DATABASE database_name
  8. SET TABLESPACE new_tablespace;
  9. ALTER DATABASE database_name
  10. SET configuration_parameter { { TO | = } { value | DEFAULT } | FROM CURRENT };
  11. ALTER DATABASE database_name
  12. RESET { configuration_parameter | ALL };
  13. ALTER DATABASE database_name
  14. [ WITH ] { ENABLE | DISABLE } PRIVATE OBJECT;

ALTER DEFAULT PRIVILEGES

设置应用于将来创建的对象的权限(这不会影响分配到已有对象中的权限)。

  1. ALTER DEFAULT PRIVILEGES
  2. [ FOR { ROLE | USER } target_role [, ...] ]
  3. [ IN SCHEMA schema_name [, ...] ]
  4. abbreviated_grant_or_revoke;
  5. where abbreviated_grant_or_revoke can be:
  6. grant_on_tables_clause
  7. | grant_on_sequences_clause
  8. | grant_on_functions_clause
  9. | grant_on_types_clause
  10. | grant_on_client_master_keys_clause
  11. | grant_on_column_encryption_keys_clause
  12. | revoke_on_tables_clause
  13. | revoke_on_sequences_clause
  14. | revoke_on_functions_clause
  15. | revoke_on_types_clause
  16. | revoke_on_client_master_keys_clause
  17. | revoke_on_column_encryption_keys_clause
  18. where grant_on_tables_clause can be:
  19. GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES |
  20. ALTER | DROP | COMMENT | INDEX | VACUUM } [, ...] | ALL [ PRIVILEGES ] }
  21. ON TABLES
  22. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  23. [ WITH GRANT OPTION ]
  24. where grant_on_sequences_clause can be:
  25. GRANT { { SELECT | UPDATE | USAGE | ALTER | DROP | COMMENT }
  26. [, ...] | ALL [ PRIVILEGES ] }
  27. ON SEQUENCES
  28. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  29. [ WITH GRANT OPTION ]
  30. where grant_on_functions_clause can be:
  31. GRANT { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
  32. ON FUNCTIONS
  33. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  34. [ WITH GRANT OPTION ]
  35. where grant_on_types_clause can be:
  36. GRANT { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
  37. ON TYPES
  38. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  39. [ WITH GRANT OPTION ]
  40. where grant_on_client_master_keys_clause can be:
  41. GRANT { { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
  42. ON CLIENT_MASTER_KEYS
  43. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  44. [ WITH GRANT OPTION ]
  45. where grant_on_column_encryption_keys_clause can be:
  46. GRANT { { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
  47. ON COLUMN_ENCRYPTION_KEYS
  48. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  49. [ WITH GRANT OPTION ]
  50. where revoke_on_tables_clause can be:
  51. REVOKE [ GRANT OPTION FOR ]
  52. { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES |
  53. ALTER | DROP | COMMENT | INDEX | VACUUM } [, ...] | ALL [ PRIVILEGES ] }
  54. ON TABLES
  55. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  56. [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]
  57. where revoke_on_sequences_clause can be:
  58. REVOKE [ GRANT OPTION FOR ]
  59. { { SELECT | UPDATE | USAGE | ALTER | DROP | COMMENT }
  60. [, ...] | ALL [ PRIVILEGES ] }
  61. ON SEQUENCES
  62. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  63. [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]
  64. where revoke_on_functions_clause can be:
  65. REVOKE [ GRANT OPTION FOR ]
  66. { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
  67. ON FUNCTIONS
  68. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  69. [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]
  70. where revoke_on_types_clause can be:
  71. REVOKE [ GRANT OPTION FOR ]
  72. { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
  73. ON TYPES
  74. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  75. [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]
  76. where revoke_on_client_master_keys_clause can be:
  77. REVOKE [ GRANT OPTION FOR ]
  78. { { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
  79. ON CLIENT_MASTER_KEYS
  80. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  81. [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]
  82. where revoke_on_column_encryption_keys_clause can be:
  83. REVOKE [ GRANT OPTION FOR ]
  84. { { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
  85. ON COLUMN_ENCRYPTION_KEYS
  86. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  87. [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]

ALTER DIRECTORY

对directory属性进行修改。

  1. CREATE [OR REPLACE] DIRECTORY directory_name
  2. AS 'path_name';

ALTER EXTENSION

修改插件扩展。

  1. ALTER EXTENSION name UPDATE [ TO new_version ];
  2. ALTER EXTENSION name SET SCHEMA new_schema;
  3. ALTER EXTENSION name ADD member_object;
  4. ALTER EXTENSION name DROP member_object;
  5. where member_object is:
  6. FOREIGN TABLE object_name |
  7. FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) |
  8. [ PROCEDURAL ] LANGUAGE object_name |
  9. SCHEMA object_name |
  10. SERVER object_name |
  11. TABLE object_name |
  12. TEXT SEARCH CONFIGURATION object_name |
  13. TYPE object_name |
  14. VIEW object_name

ALTER FOREIGN TABLE

对外表进行修改。

  1. 1. GDS:
  2. ALTER FOREIGN TABLE [ IF EXISTS ] table_name
  3. OPTIONS ( {[ ADD | SET | DROP ] option ['value']} [, ... ]);
  4. ALTER FOREIGN TABLE [ IF EXISTS ] tablename
  5. OWNER TO new_owner;
  6. 2. HDFS:
  7. ALTER FOREIGN TABLE [ IF EXISTS ] table_name
  8. OPTIONS ( {[ ADD | SET | DROP ] option ['value']} [, ... ]);
  9. ALTER FOREIGN TABLE [ IF EXISTS ] tablename
  10. OWNER TO new_owner;
  11. ALTER FOREIGN TABLE [ IF EXISTS ] table_name
  12. MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] );
  13. ALTER FOREIGN TABLE [ IF EXISTS ] tablename
  14. ADD [CONSTRAINT constraint_name]
  15. {PRIMARY KEY | UNIQUE} (column_name)
  16. [NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED];
  17. ALTER FOREIGN TABLE [ IF EXISTS ] tablename
  18. DROP CONSTRAINT constraint_name ;
  19. ALTER FOREIGN TABLE [ IF EXISTS ] tablename
  20. action [, ... ];
  21. where action can be:
  22. ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type
  23. | ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
  24. | ALTER [ COLUMN ] column_name SET STATISTICS integer
  25. | ALTER [ COLUMN ] column_name OPTIONS ( {[ ADD | SET | DROP ] option ['value'] } [, ... ])
  26. | MODIFY column_name data_type
  27. | MODIFY column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ]
  28. | MODIFY column_name [ CONSTRAINT constraint_name ] NULL
  29. 3. OBS:
  30. ALTER FOREIGN TABLE [ IF EXISTS ] table_name
  31. OPTIONS ( {[ ADD | SET | DROP ] option ['value']} [, ... ]);
  32. ALTER FOREIGN TABLE [ IF EXISTS ] tablename
  33. OWNER TO new_owner;
  34. ALTER FOREIGN TABLE [ IF EXISTS ] table_name
  35. MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] );
  36. ALTER FOREIGN TABLE [ IF EXISTS ] tablename
  37. ADD [CONSTRAINT constraint_name]
  38. {PRIMARY KEY | UNIQUE} (column_name)
  39. [NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED];
  40. ALTER FOREIGN TABLE [ IF EXISTS ] tablename
  41. DROP CONSTRAINT constraint_name ;
  42. ALTER FOREIGN TABLE [ IF EXISTS ] tablename
  43. action [, ... ];
  44. where action can be:
  45. ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type
  46. | ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
  47. | ALTER [ COLUMN ] column_name SET STATISTICS integer
  48. | ALTER [ COLUMN ] column_name OPTIONS ( {[ ADD | SET | DROP ] option ['value'] } [, ... ])
  49. | MODIFY column_name data_type
  50. | MODIFY column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ]
  51. | MODIFY column_name [ CONSTRAINT constraint_name ] NULL
  52. 4. GC:
  53. ALTER FOREIGN TABLE [ IF EXISTS ] tablename
  54. OPTIONS ( {[ SET ] option ['value']} [, ... ]);
  55. ALTER FOREIGN TABLE [ IF EXISTS ] tablename
  56. OWNER TO new_owner;
  57. ALTER FOREIGN TABLE [ IF EXISTS ] table_name
  58. MODIFY ( { column_name data_type [, ...] );
  59. ALTER FOREIGN TABLE [ IF EXISTS ] tablename
  60. action [, ... ];
  61. where action can be:
  62. ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type
  63. | MODIFY column_name data_type

ALTER FUNCTION

修改自定义函数的属性。

  1. ALTER FUNCTION function_name ( [ {[ argmode ] [ argname ] argtype} [, ...] ] )
  2. action [ ... ] [ RESTRICT ];
  3. ALTER FUNCTION funname ( [ {[ argmode ] [ argname ] argtype} [, ...] ] )
  4. RENAME TO new_name;
  5. ALTER FUNCTION funname ( [ {[ argmode ] [ argname ] argtype} [, ...] ] )
  6. OWNER TO new_owner;
  7. ALTER FUNCTION funname ( [ {[ argmode ] [ argname ] argtype} [, ...] ] )
  8. SET SCHEMA new_schema;
  9. where action can be:
  10. {CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT}
  11. | {IMMUTABLE | STABLE | VOLATILE}
  12. | {NOT FENCED | FENCED}
  13. | [ NOT ] LEAKPROOF
  14. | {[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER}
  15. | AUTHID { DEFINER | CURRENT_USER }
  16. | COST execution_cost
  17. | ROWS result_rows
  18. | SET configuration_parameter {{ TO | = } { value | DEFAULT }| FROM CURRENT}
  19. | RESET {configuration_parameter| ALL}

ALTER GROUP

修改一个用户组的属性。

  1. ALTER GROUP group_name
  2. ADD USER user_name [, ... ];
  3. ALTER GROUP group_name
  4. DROP USER user_name [, ... ];
  5. ALTER GROUP group_name
  6. RENAME TO new_name;

ALTER INDEX

修改现有索引的定义。

  1. ALTER INDEX [ IF EXISTS ] index_name
  2. RENAME TO new_name;
  3. ALTER INDEX [ IF EXISTS ] index_name
  4. SET TABLESPACE tablespace_name;
  5. ALTER INDEX [ IF EXISTS ] index_name
  6. SET ( {storage_parameter = value} [, ... ] );
  7. ALTER INDEX [ IF EXISTS ] index_name
  8. RESET ( storage_parameter [, ... ] ) ;
  9. ALTER INDEX [ IF EXISTS ] index_name
  10. [ MODIFY PARTITION partition_name ] UNUSABLE;
  11. ALTER INDEX index_name
  12. REBUILD [ PARTITION partition_name ];
  13. ALTER INDEX [ IF EXISTS ] index_name
  14. RENAME PARTITION partition_name TO new_partition_name;
  15. ALTER INDEX [ IF EXISTS ] index_name
  16. MOVE PARTITION index_partition_name TABLESPACE new_tablespace;

ALTER LARGE OBJECT

用于更改一个large object的定义。它的唯一的功能是分配一个新的所有者。

  1. ALTER LARGE OBJECT large_object_oid
  2. OWNER TO new_owner;

ALTER MASKING POLICY

修改脱敏策略。

  1. ALTER MASKING POLICY policy_name { ADD | REMOVE | MODIFY } masking_actions [, ... ];
  2. ALTER MASKING POLICY policy_name MODIFY ( filter_group_clause );
  3. ALTER MASKING POLICY policy_name DROP FILTER;
  4. ALTER MASKING POLICY policy_name { ENABLE | DISABLE };
  5. where masking_actions can be:
  6. masking_function ON LABEL(label_name [, ... ])
  7. where masking_function can be:
  8. { maskall | randommasking | creditcardmasking | basicemailmasking | fullemailmasking | shufflemasking | alldigitsmasking | regexpmasking }
  9. where filter_group_clause can be:
  10. FILTER ON { ( FILTER_TYPE ( filter_value [, ... ] ) ) [, ... ] }

ALTER MATERIALIZED VIEW

更改一个现有物化视图的多个辅助属性。

  1. ALTER MATERIALIZED VIEW [ IF EXISTS ] mv_name
  2. OWNER TO new_owner;
  3. ALTER MATERIALIZED VIEW [ IF EXISTS ] mv_name
  4. RENAME [COLUMN] column_name to new_column_name;
  5. ALTER MATERIALIZED VIEW [ IF EXISTS ] mv_name
  6. RENAME TO new_name;

ALTER OPERATOR

修改一个操作符的定义。

  1. ALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } ) OWNER TO new_owner
  2. ALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } ) SET SCHEMA new_schema

ALTER RESOURCE LABEL

修改资源标签。

  1. ALTER RESOURCE LABEL label_name { ADD | REMOVE } label_item_list [, ... ];
  2. where label_item_list can be:
  3. resource_type(resource_path[, ... ])
  4. where resource_type can be:
  5. { TABLE | COLUMN | SCHEMA | VIEW | FUNCTION }

ALTER RESOURCE POOL

修改一个资源池,指定其他控制组。

  1. ALTER RESOURCE POOL pool_name
  2. WITH ({MEM_PERCENT=pct | CONTROL_GROUP="group_name" | ACTIVE_STATEMENTS=stmt | MAX_DOP = dop | MEMORY_LIMIT='memory_size' | io_limits=io_limits | io_priority='priority' | nodegroup='nodegroup_name' }[, ... ]);

ALTER ROLE

修改角色属性。

  1. ALTER ROLE role_name [ [ WITH ] option [ ... ] ];
  2. ALTER ROLE role_name
  3. RENAME TO new_name;
  4. ALTER ROLE role_name [ IN DATABASE database_name ]
  5. SET configuration_parameter {{ TO | = } { value | DEFAULT }|FROM CURRENT};
  6. ALTER ROLE role_name
  7. [ IN DATABASE database_name ] RESET {configuration_parameter|ALL};
  8. where option can be:
  9. {CREATEDB | NOCREATEDB}
  10. | {CREATEROLE | NOCREATEROLE}
  11. | {INHERIT | NOINHERIT}
  12. | {AUDITADMIN | NOAUDITADMIN}
  13. | {SYSADMIN | NOSYSADMIN}
  14. | {MONADMIN | NOMONADMIN}
  15. | {OPRADMIN | NOOPRADMIN}
  16. | {POLADMIN | NOPOLADMIN}
  17. | {USEFT | NOUSEFT}
  18. | {LOGIN | NOLOGIN}
  19. | {REPLICATION | NOREPLICATION}
  20. | {INDEPENDENT | NOINDEPENDENT}
  21. | {VCADMIN | NOVCADMIN}
  22. | {PERSISTENCE | NOPERSISTENCE}
  23. | CONNECTION LIMIT connlimit
  24. | [ ENCRYPTED | UNENCRYPTED ] PASSWORD { 'password' [ EXPIRED ] | DISABLE | EXPIRED }
  25. | [ ENCRYPTED | UNENCRYPTED ] IDENTIFIED BY { 'password' [ REPLACE 'old_password' | EXPIRED ] | DISABLE }
  26. | VALID BEGIN 'timestamp'
  27. | VALID UNTIL 'timestamp'
  28. | RESOURCE POOL 'respool'
  29. | USER GROUP 'groupuser'
  30. | PERM SPACE 'spacelimit'
  31. | TEMP SPACE 'tmpspacelimit'
  32. | SPILL SPACE 'spillspacelimit'
  33. | NODE GROUP logic_cluster_name
  34. | ACCOUNT { LOCK | UNLOCK }
  35. | PGUSER

ALTER ROW LEVEL SECURITY POLICY

对已存在的行访问控制策略(包括行访问控制策略的名称,行访问控制指定的用户,行访问控制的策略表达式)进行修改。

  1. ALTER [ ROW LEVEL SECURITY ] POLICY [ IF EXISTS ] policy_name ON table_name RENAME TO new_policy_name
  2. ALTER [ ROW LEVEL SECURITY ] POLICY policy_name ON table_name
  3. [ TO { role_name | PUBLIC } [, ...] ]
  4. [ USING ( using_expression ) ]

ALTER SCHEMA

修改模式属性。

  1. ALTER SCHEMA schema_name
  2. RENAME TO new_name;
  3. ALTER SCHEMA schema_name
  4. OWNER TO new_owner;
  5. ALTER SCHEMA schema_name {WITH | WITHOUT} BLOCKCHAIN;

ALTER SEQUENCE

修改一个现有的序列的参数。

  1. ALTER SEQUENCE [ IF EXISTS ] name
  2. [ MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE ]
  3. [ OWNED BY { table_name.column_name | NONE } ];
  4. ALTER SEQUENCE [ IF EXISTS ] name OWNER TO new_owner;

ALTER SERVER

增加、修改和删除一个现有server的参数。已有server可以从pg_foreign_server系统表中查询。

  1. ALTER SERVER server_name [ VERSION 'new_version' ]
  2. [ OPTIONS ( {[ ADD | SET | DROP ] option ['value']} [, ... ] ) ];
  3. ALTER SERVER server_name
  4. OWNER TO new_owner;
  5. ALTER SERVER server_name
  6. RENAME TO new_name;

ALTER SESSION

ALTER SESSION命令用于定义或修改那些对当前会话有影响的条件或参数。修改后的会话参数会一直保持,直到断开当前会话。

  1. ALTER SESSION SET
  2. {{config_parameter { { TO | = } { value | DEFAULT }
  3. | FROM CURRENT }} | CURRENT_SCHEMA [ TO | = ] { schema | DEFAULT }
  4. | TIME ZONE time_zone
  5. | SCHEMA schema
  6. | NAMES encoding_name
  7. | ROLE role_name PASSWORD 'password'
  8. | SESSION AUTHORIZATION { role_name PASSWORD 'password' | DEFAULT }
  9. | XML OPTION { DOCUMENT | CONTENT }
  10. } ;
  11. ALTER SESSION SET [ SESSION CHARACTERISTICS AS ] TRANSACTION
  12. { ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED } | { READ ONLY | READ WRITE } } [, ...] ;

ALTER SYNONYM

修改SYNONYM对象的属性。

  1. ALTER SYNONYM synonym_name
  2. OWNER TO new_owner;

ALTER SYSTEM KILL SESSION

用于结束一个会话。

  1. ALTER SYSTEM KILL SESSION 'session_sid, serial' [ IMMEDIATE ];

ALTER SYSTEM SET

用于设置POSTMASTER、SIGHUP、BACKEND级别的GUC参数。此命令会将参数写入配置文件,不同级别生效方式有所不同。

  1. ALTER SYSTEM SET { GUC_name } TO { GUC_value };

ALTER TABLE

修改表,包括修改表的定义、重命名表、重命名表中指定的列、重命名表的约束、设置表的所属模式、添加/更新多个列、打开/关闭行访问控制开关。

  1. ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )}
  2. action [, ... ];
  3. ALTER TABLE [ IF EXISTS ] table_name
  4. ADD ( { column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]} [, ...] );
  5. ALTER TABLE [ IF EXISTS ] table_name
  6. MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] );
  7. ALTER TABLE [ IF EXISTS ] table_name
  8. RENAME TO new_table_name;
  9. ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )}
  10. RENAME [ COLUMN ] column_name TO new_column_name;
  11. ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )}
  12. RENAME CONSTRAINT constraint_name TO new_constraint_name;
  13. ALTER TABLE [ IF EXISTS ] table_name
  14. SET SCHEMA new_schema;
  15. where action can be:
  16. column_clause
  17. | ADD table_constraint [ NOT VALID ]
  18. | ADD table_constraint_using_index
  19. | VALIDATE CONSTRAINT constraint_name
  20. | DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
  21. | CLUSTER ON index_name
  22. | SET WITHOUT CLUSTER
  23. | SET ( {storage_parameter = value} [, ... ] )
  24. | RESET ( storage_parameter [, ... ] )
  25. | OWNER TO new_owner
  26. | SET TABLESPACE new_tablespace
  27. | SET {COMPRESS|NOCOMPRESS}
  28. | TO { GROUP groupname | NODE ( nodename [, ... ] ) }
  29. | ADD NODE ( nodename [, ... ] )
  30. | DELETE NODE ( nodename [, ... ] )
  31. | UPDATE SLICE LIKE table_name
  32. | DISABLE TRIGGER [ trigger_name | ALL | USER ]
  33. | ENABLE TRIGGER [ trigger_name | ALL | USER ]
  34. | ENABLE REPLICA TRIGGER trigger_name
  35. | ENABLE ALWAYS TRIGGER trigger_name
  36. | ENABLE ROW LEVEL SECURITY
  37. | DISABLE ROW LEVEL SECURITY
  38. | FORCE ROW LEVEL SECURITY
  39. | NO FORCE ROW LEVEL SECURITY
  40. | ENCRYPTION KEY ROTATION
  41. where column_clause can be:
  42. ADD [ COLUMN ] column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]
  43. | MODIFY column_name data_type
  44. | MODIFY column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ]
  45. | MODIFY column_name [ CONSTRAINT constraint_name ] NULL
  46. | DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
  47. | ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
  48. | ALTER [ COLUMN ] column_name { SET DEFAULT expression | DROP DEFAULT }
  49. | ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
  50. | ALTER [ COLUMN ] column_name SET STATISTICS [PERCENT] integer
  51. | ADD STATISTICS (( column_1_name, column_2_name [, ...] ))
  52. | DELETE STATISTICS (( column_1_name, column_2_name [, ...] ))
  53. | ALTER [ COLUMN ] column_name SET ( {attribute_option = value} [, ... ] )
  54. | ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
  55. | ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
  56. where column_constraint can be:
  57. [ CONSTRAINT constraint_name ]
  58. { NOT NULL |
  59. NULL |
  60. CHECK ( expression ) |
  61. DEFAULT default_expr |
  62. GENERATED ALWAYS AS ( generation_expr ) STORED |
  63. UNIQUE index_parameters |
  64. PRIMARY KEY index_parameters |
  65. ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = column_encryption_key, ENCRYPTION_TYPE = encryption_type_value ) |
  66. REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
  67. [ ON DELETE action ] [ ON UPDATE action ] }
  68. [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  69. where compress_mode can be:
  70. { DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS }
  71. where table_constraint can be:
  72. [ CONSTRAINT constraint_name ]
  73. { CHECK ( expression ) |
  74. UNIQUE ( column_name [, ... ] ) index_parameters |
  75. PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  76. PARTIAL CLUSTER KEY ( column_name [, ... ] ) |
  77. FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
  78. [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
  79. [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  80. where index_parameters can be:
  81. [ WITH ( {storage_parameter = value} [, ... ] ) ]
  82. [ USING INDEX TABLESPACE tablespace_name ]
  83. where table_constraint_using_index can be:
  84. [ CONSTRAINT constraint_name ]
  85. { UNIQUE | PRIMARY KEY } USING INDEX index_name
  86. [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

ALTER TABLE PARTITION

  1. ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )}
  2. action [, ... ];
  3. ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )}
  4. RENAME PARTITION { partion_name | FOR ( partition_value [, ...] ) } TO partition_new_name;
  5. where action can be:
  6. move_clause |
  7. exchange_clause |
  8. row_clause |
  9. merge_clause |
  10. modify_clause |
  11. split_clause |
  12. add_clause |
  13. drop_clause
  14. where move_clause can be:
  15. MOVE PARTITION { partion_name | FOR ( partition_value [, ...] ) } TABLESPACE tablespacename
  16. where exchange_clause can be:
  17. EXCHANGE PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) }
  18. WITH TABLE {[ ONLY ] ordinary_table_name | ordinary_table_name * | ONLY ( ordinary_table_name )}
  19. [ { WITH | WITHOUT } VALIDATION ] [ VERBOSE ]
  20. where row_clause can be:
  21. { ENABLE | DISABLE } ROW MOVEMENT
  22. where merge_clause can be:
  23. MERGE PARTITIONS { partition_name } [, ...] INTO PARTITION partition_name
  24. [ TABLESPACE tablespacename ]
  25. where modify_clause can be:
  26. MODIFY PARTITION partition_name { UNUSABLE LOCAL INDEXES | REBUILD UNUSABLE LOCAL INDEXES }
  27. where split_clause can be:
  28. SPLIT PARTITION { partition_name | FOR ( partition_value [, ...] ) } { split_point_clause | no_split_point_clause }
  29. where split_point_clause can be:
  30. AT ( partition_value ) INTO ( PARTITION partition_name [ TABLESPACE tablespacename ] , PARTITION partition_name [ TABLESPACE tablespacename ] )
  31. where no_split_point_clause can be:
  32. INTO {(partition_less_than_item [, ...] ) | (partition_start_end_item [, ...] )}
  33. where add_clause can be:
  34. ADD {partition_less_than_item | partition_start_end_item}
  35. where partition_less_than_item can be:
  36. PARTITION partition_name VALUES LESS THAN ( { partition_value | MAXVALUE } [, ...] ) [ TABLESPACE tablespacename ]
  37. where partition_start_end_item can be:
  38. PARTITION partition_name {
  39. {START(partition_value) END (partition_value) EVERY (interval_value)} |
  40. {START(partition_value) END ({partition_value | MAXVALUE})} |
  41. {START(partition_value)} |
  42. {END({partition_value | MAXVALUE})}
  43. } [TABLESPACE tablespace_name]
  44. where drop_clause can be:
  45. DROP PARTITION { partition_name | FOR ( partition_value [, ...] ) }

ALTER TABLESPACE

修改表空间的属性。

  1. ALTER TABLESPACE tablespace_name
  2. RENAME TO new_tablespace_name;
  3. ALTER TABLESPACE tablespace_name
  4. OWNER TO new_owner;
  5. ALTER TABLESPACE tablespace_name
  6. SET ( {tablespace_option = value} [, ... ] );
  7. ALTER TABLESPACE tablespace_name
  8. RESET ( tablespace_option [, ... ] );
  9. ALTER TABLESPACE tablespace_name
  10. RESIZE MAXSIZE { UNLIMITED | 'space_size' };

ALTER TEXT SEARCH CONFIGURATION

更改文本搜索配置的定义。用户可以将映射从字串类型调整为字典,或者改变配置的名称或者所有者,或者修改搜索配置的配置参数。

  1. ALTER TEXT SEARCH CONFIGURATION name
  2. ADD MAPPING FOR token_type [, ... ] WITH dictionary_name [, ... ]
  3. ALTER TEXT SEARCH CONFIGURATION name
  4. ALTER MAPPING FOR token_type [, ... ] WITH dictionary_name [, ... ]
  5. ALTER TEXT SEARCH CONFIGURATION name
  6. ALTER MAPPING REPLACE old_dictionary WITH new_dictionary
  7. ALTER TEXT SEARCH CONFIGURATION name
  8. ALTER MAPPING FOR token_type [, ... ] REPLACE old_dictionary WITH new_dictionary
  9. ALTER TEXT SEARCH CONFIGURATION name
  10. DROP MAPPING [ IF EXISTS ] FOR token_type [, ... ]
  11. ALTER TEXT SEARCH CONFIGURATION name RENAME TO new_name
  12. ALTER TEXT SEARCH CONFIGURATION name OWNER TO new_owner
  13. ALTER TEXT SEARCH CONFIGURATION name SET SCHEMA new_schema
  14. ALTER TEXT SEARCH CONFIGURATION name SET ( {configuration_option = value} [, ...] )
  15. ALTER TEXT SEARCH CONFIGURATION name RESET ( {configuration_option} [, ...] )

ALTER TEXT SEARCH DICTIONARY

修改全文检索词典的相关定义,包括参数、名称、所有者、以及模式等。

  1. ALTER TEXT SEARCH DICTIONARY name ( option = value | option [, ...] );
  2. ALTER TEXT SEARCH DICTIONARY name RENAME TO new_name;
  3. ALTER TEXT SEARCH DICTIONARY name OWNER TO new_owner;
  4. ALTER TEXT SEARCH DICTIONARY name SET SCHEMA new_schema

ALTER TRIGGER

修改触发器名称。

  1. ALTER TRIGGER name ON table_name RENAME TO new_name

ALTER TYPE

修改一个类型的定义。

  1. ALTER TYPE name action [, ... ]
  2. ALTER TYPE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
  3. ALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name [ CASCADE | RESTRICT ]
  4. ALTER TYPE name RENAME TO new_name
  5. ALTER TYPE name SET SCHEMA new_schema
  6. ALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } neighbor_enum_value ]
  7. ALTER TYPE name RENAME VALUE existing_enum_value TO new_enum_value
  8. where action is one of:
  9. ADD ATTRIBUTE attribute_name data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]
  10. DROP ATTRIBUTE [ IF EXISTS ] attribute_name [ CASCADE | RESTRICT ]
  11. ALTER ATTRIBUTE attribute_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]

ALTER USER

修改数据库用户的属性。

  1. ALTER USER user_name [ [ WITH ] option [ ... ] ];
  2. ALTER USER user_name
  3. RENAME TO new_name;
  4. ALTER USER user_name [ IN DATABASE database_name ]
  5. SET configuration_parameter {{ TO | = } { value | DEFAULT }|FROM CURRENT};
  6. ALTER USER user_name
  7. [ IN DATABASE database_name ] RESET {configuration_parameter|ALL};
  8. where option can be:
  9. {CREATEDB | NOCREATEDB}
  10. | {CREATEROLE | NOCREATEROLE}
  11. | {INHERIT | NOINHERIT}
  12. | {AUDITADMIN | NOAUDITADMIN}
  13. | {SYSADMIN | NOSYSADMIN}
  14. | {MONADMIN | NOMONADMIN}
  15. | {OPRADMIN | NOOPRADMIN}
  16. | {POLADMIN | NOPOLADMIN}
  17. | {USEFT | NOUSEFT}
  18. | {LOGIN | NOLOGIN}
  19. | {REPLICATION | NOREPLICATION}
  20. | {INDEPENDENT | NOINDEPENDENT}
  21. | {VCADMIN | NOVCADMIN}
  22. | {PERSISTENCE | NOPERSISTENCE}
  23. | CONNECTION LIMIT connlimit
  24. | [ ENCRYPTED | UNENCRYPTED ] PASSWORD { 'password' [ EXPIRED ] | DISABLE | EXPIRED }
  25. | [ ENCRYPTED | UNENCRYPTED ] IDENTIFIED BY { 'password' [ REPLACE 'old_password' | EXPIRED ] | DISABLE }
  26. | VALID BEGIN 'timestamp'
  27. | VALID UNTIL 'timestamp'
  28. | RESOURCE POOL 'respool'
  29. | USER GROUP 'groupuser'
  30. | PERM SPACE 'spacelimit'
  31. | TEMP SPACE 'tmpspacelimit'
  32. | SPILL SPACE 'spillspacelimit'
  33. | NODE GROUP logic_cluster_name
  34. | ACCOUNT { LOCK | UNLOCK }
  35. | PGUSER

ALTER VIEW

更改视图的各种辅助属性。

  1. ALTER VIEW [ IF EXISTS ] view_name
  2. ALTER [ COLUMN ] column_name SET DEFAULT expression;
  3. ALTER VIEW [ IF EXISTS ] view_name
  4. ALTER [ COLUMN ] column_name DROP DEFAULT;
  5. ALTER VIEW [ IF EXISTS ] view_name
  6. OWNER TO new_owner;
  7. ALTER VIEW [ IF EXISTS ] view_name
  8. RENAME TO new_name;
  9. ALTER VIEW [ IF EXISTS ] view_name
  10. SET SCHEMA new_schema;
  11. ALTER VIEW [ IF EXISTS ] view_name
  12. SET ( {view_option_name [= view_option_value]} [, ... ] );
  13. ALTER VIEW [ IF EXISTS ] view_name
  14. RESET ( view_option_name [, ... ] );

ANALYSE|ANALYZE

用于收集与数据库中普通表内容相关的统计信息,统计结果存储在系统表PG_STATISTIC下。执行计划生成器会使用这些统计数据,以确定最有效的执行计划。

  1. {ANALYZE | ANALYSE} [ VERBOSE ]
  2. [ table_name [ ( column_name [, ...] ) ] ];
  3. {ANALYZE | ANALYSE} [ VERBOSE ]
  4. [ table_name [ ( column_name [, ...] ) ] ]
  5. PARTITION patrition_name;
  6. {ANALYZE | ANALYSE} [ VERBOSE ]
  7. { foreign_table_name | FOREIGN TABLES };
  8. {ANALYZE | ANALYSE} [ VERBOSE ]
  9. table_name (( column_1_name, column_2_name [, ...] ));
  10. {ANALYZE | ANALYSE} VERIFY {FAST|COMPLETE};
  11. {ANALYZE | ANALYSE} VERIFY {FAST|COMPLETE}
  12. table_name|index_name [CASCADE];
  13. {ANALYZE | ANALYSE} VERIFY {FAST|COMPLETE}
  14. table_name PARTITION (patrition_name) [CASCADE];

ANONYMOUS BLOCK

匿名块(Anonymous Block)一般用于不频繁执行的脚本或不重复进行的活动。它们在一个会话中执行,并不被存储。

  1. [DECLARE [declare_statements]]
  2. BEGIN
  3. execution_staements
  4. END;
  5. /

BEGIN

BEGIN可以用于开始一个匿名块,也可以用于开始一个事务。

  1. start a anonymous block:
  2. [DECLARE [declare_statements]]
  3. BEGIN
  4. execution_statements
  5. END;
  6. /
  7. start a transaction:
  8. BEGIN [ WORK | TRANSACTION ]
  9. [
  10. {
  11. ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE | REPEATABLE READ }
  12. | { READ WRITE | READ ONLY }
  13. } [, ...]
  14. ];

CALL

可以调用已定义的函数和存储过程。

  1. CALL [schema.] func_name ( param_expr );

CHECKPOINT

检查点(CHECKPOINT)是一个事务日志中的点,所有数据文件都在该点被更新以反映日志中的信息,所有数据文件都将被刷新到磁盘。

  1. CHECKPOINT

CLEAN CONNECTION

用来清理数据库连接。允许在节点上清理指定数据库的指定用户的相关连接。

  1. CLEAN CONNECTION
  2. TO { COORDINATOR ( nodename [, ... ] ) | NODE ( nodename [, ... ] ) | ALL [ CHECK ] [ FORCE ] }
  3. [ FOR DATABASE dbname ]
  4. [ TO USER username ];

CLOSE

释放和一个游标关联的所有资源。

  1. CLOSE { cursor_name | ALL };

CLUSTER

根据一个索引对表进行聚簇排序。

  1. CLUSTER [ VERBOSE ] table_name [ USING index_name ];
  2. CLUSTER [ VERBOSE ] table_name PARTITION ( partition_name ) [ USING index_name ];
  3. CLUSTER [ VERBOSE ];

COMMENT

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

  1. COMMENT ON
  2. {
  3. AGGREGATE agg_name (agg_type [, ...] ) |
  4. CAST (source_type AS target_type) |
  5. COLLATION object_name |
  6. COLUMN { table_name.column_name | view_name.column_name } |
  7. CONSTRAINT constraint_name ON table_name |
  8. CONVERSION object_name |
  9. DATABASE object_name |
  10. DOMAIN object_name |
  11. EXTENSION object_name |
  12. FOREIGN DATA WRAPPER object_name |
  13. FOREIGN TABLE object_name |
  14. FUNCTION function_name ( [ {[ argmode ] [ argname ] argtype} [, ...] ] ) |
  15. INDEX object_name |
  16. LARGE OBJECT large_object_oid |
  17. OPERATOR operator_name (left_type, right_type) |
  18. OPERATOR CLASS object_name USING index_method |
  19. OPERATOR FAMILY object_name USING index_method |
  20. [ PROCEDURAL ] LANGUAGE object_name |
  21. ROLE object_name |
  22. RULE rule_name ON table_name |
  23. SCHEMA object_name |
  24. SERVER object_name |
  25. TABLE object_name |
  26. TABLESPACE object_name |
  27. TEXT SEARCH CONFIGURATION object_name |
  28. TEXT SEARCH DICTIONARY object_name |
  29. TEXT SEARCH PARSER object_name |
  30. TEXT SEARCH TEMPLATE object_name |
  31. TYPE object_name |
  32. VIEW object_name
  33. }
  34. IS 'text';

COMMIT

通过COMMIT或者END可完成提交事务的功能,即提交事务的所有操作。

  1. { COMMIT | END } [ WORK | TRANSACTION ];

COMMIT PREPARED

提交一个早先为两阶段提交准备好的事务。

  1. COMMIT PREPARED transaction_id;

COPY

通过COPY命令实现在表和文件之间拷贝数据。

  1. COPY table_name [ ( column_name [, ...] ) ]
  2. FROM { 'filename' | STDIN }
  3. [ [ USING ] DELIMITERS 'delimiters' ]
  4. [ WITHOUT ESCAPING ]
  5. [ LOG ERRORS ]
  6. [ LOG ERRORS DATA ]
  7. [ REJECT LIMIT 'limit' ]
  8. [ [ WITH ] ( option [, ...] ) ]
  9. | copy_option
  10. | [ FIXED FORMATTER ( { column_name( offset, length ) } [, ...] ) ]
  11. | [ TRANSFORM ( { column_name [ data_type ] [ AS transform_expr ] } [, ...] ) ];
  12. COPY table_name [ ( column_name [, ...] ) ]
  13. TO { 'filename' | STDOUT }
  14. [ [ USING ] DELIMITERS 'delimiters' ]
  15. [ WITHOUT ESCAPING ]
  16. [ [ WITH ] ( option [, ...] ) ]
  17. | copy_option
  18. | [ FIXED FORMATTER ( { column_name( offset, length ) } [, ...] ) ];
  19. COPY query
  20. TO { 'filename' | STDOUT }
  21. [ WITHOUT ESCAPING ]
  22. [ [ WITH ] ( option [, ...] ) ]
  23. | copy_option
  24. | [ FIXED FORMATTER ( { column_name( offset, length ) } [, ...] ) ];
  25. where option can be:
  26. FORMAT 'format_name'
  27. | OIDS [ boolean ]
  28. | DELIMITER 'delimiter_character'
  29. | NULL 'null_string'
  30. | HEADER [ boolean ]
  31. | FILEHEADER 'header_file_string'
  32. | FREEZE [ boolean ]
  33. | QUOTE 'quote_character'
  34. | ESCAPE 'escape_character'
  35. | EOL 'newline_character'
  36. | NOESCAPING [ boolean ]
  37. | FORCE_QUOTE { ( column_name [, ...] ) | * }
  38. | FORCE_NOT_NULL ( column_name [, ...] )
  39. | FORCE_NULL ( column_name [, ...] )
  40. | ENCODING 'encoding_name'
  41. | IGNORE_EXTRA_DATA [ boolean ]
  42. | FILL_MISSING_FIELDS [ boolean ]
  43. | COMPATIBLE_ILLEGAL_CHARS [ boolean ]
  44. | DATE_FORMAT 'date_format_string'
  45. | TIME_FORMAT 'time_format_string'
  46. | TIMESTAMP_FORMAT 'timestamp_format_string'
  47. | SMALLDATETIME_FORMAT 'smalldatetime_format_string'
  48. and copy_option can be:
  49. OIDS
  50. | NULL 'null_string'
  51. | HEADER
  52. | FILEHEADER 'header_file_string'
  53. | FREEZE
  54. | FORCE NOT NULL column_name [, ...]
  55. | FORCE NULL column_name [, ...]
  56. | FORCE QUOTE { column_name [, ...] | * }
  57. | BINARY
  58. | CSV
  59. | QUOTE [ AS ] 'quote_character'
  60. | ESCAPE [ AS ] 'escape_character'
  61. | EOL 'newline_character'
  62. | ENCODING 'encoding_name'
  63. | IGNORE_EXTRA_DATA
  64. | FILL_MISSING_FIELDS
  65. | COMPATIBLE_ILLEGAL_CHARS
  66. | DATE_FORMAT 'date_format_string'
  67. | TIME_FORMAT 'time_format_string'
  68. | TIMESTAMP_FORMAT 'timestamp_format_string'
  69. | SMALLDATETIME_FORMAT 'smalldatetime_format_string'

CREATE AUDIT POLICY

创建统一审计策略。

  1. CREATE AUDIT POLICY [ IF NOT EXISTS ] policy_name { { privilege_audit_clause | access_audit_clause } [ filter_group_clause ] [ ENABLED | DISABLED ] };
  2. where privilege_audit_clause can be:
  3. PRIVILEGES { DDL | ALL } [ ON LABEL ( resource_label_name [, ... ] ) ]
  4. where access_audit_clause can be:
  5. ACCESS { DML | ALL } [ ON LABEL ( resource_label_name [, ... ] ) ]
  6. where filter_group_clause can be:
  7. FILTER ON { ( FILTER_TYPE ( filter_value [, ... ] ) ) [, ... ] }
  8. where DDL can be:
  9. { ( ALTER | ANALYZE | COMMENT | CREATE | DROP | GRANT | REVOKE | SET | SHOW | LOGIN_ACCESS | LOGIN_FAILURE | LOGOUT | LOGIN ) }
  10. where DML can be:
  11. { ( COPY | DEALLOCATE | DELETE_P | EXECUTE | REINDEX | INSERT | REPARE | SELECT | TRUNCATE | UPDATE ) }
  12. where FILTER_TYPE can be:
  13. { APP | ROLES | IP }

CREATE CLIENT MASTER KEY

创建一个客户端主密钥对象,该对象可用于加密Column Encryption Key对象。

  1. CREATE CLIENT MASTER KEY client_master_key_name
  2. [WITH] ( ['KEY_STORE' , 'KEY_PATH' , 'ALGORITHM'] );

CREATE COLUMN ENCRYPTION KEY

创建一个列加密密钥,该密钥可用于加密表中指定列。

  1. CREATE COLUMN ENCRYPTION KEY column_encryption_key_name
  2. [WITH] [VALUES] ( ['CLIENT_MASTER_KEY' , 'ALGORITHM'] );

CREATE DATA SOURCE

创建一个新的外部数据源对象,该对象用于定义openGauss要连接的目标库信息。

  1. CREATE DATA SOURCE src_name
  2. [TYPE 'type_str']
  3. [VERSION {'version_str' | NULL}]
  4. [OPTIONS (optname 'optvalue' [, ...])];
  5. Valid optname are:
  6. DSN, USERNAME, PASSWORD, ENCODING

CREATE DATABASE

创建一个新的数据库。缺省情况下新数据库将通过复制标准系统数据库template0来创建,且仅支持使用template0来创建。

  1. CREATE DATABASE database_name
  2. [ [ WITH ] {[ OWNER [=] user_name ]|
  3. [ TEMPLATE [=] template ]|
  4. [ ENCODING [=] encoding ]|
  5. [ LC_COLLATE [=] lc_collate ]|
  6. [ LC_CTYPE [=] lc_ctype ]|
  7. [ DBCOMPATIBILITY [=] compatibility_type ]|
  8. [ TABLESPACE [=] tablespace_name ]|
  9. [ CONNECTION LIMIT [=] connlimit ]}[...] ];

CREATE DIRECTORY

使用CREATE DIRECTORY语句创建一个目录对象,该目录对象定义了服务器文件系统上目录的别名,用于存放用户使用的数据文件。

  1. CREATE [OR REPLACE] DIRECTORY directory_name
  2. AS 'path_name';

CREATE EXTENSION

安装一个扩展。

  1. CREATE EXTENSION [ IF NOT EXISTS ] extension_name
  2. [ WITH ] [ SCHEMA schema_name ]
  3. [ VERSION version ]
  4. [ FROM old_version ];

CREATE FOREIGN TABLE

创建外表。

  1. CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name
  2. ( { column_name type_name POSITION(offset,length) [column_constraint ]
  3. | LIKE source_table | table_constraint } [, ...] )
  4. SEVER gsmpp_server
  5. OPTIONS ( { option_name ' value ' } [, ...] )
  6. [ { WRITE ONLY | READ ONLY }]
  7. [ WITH error_table_name | LOG INTO error_table_name]
  8. [REMOTE LOG 'name']
  9. [PER NODE REJECT LIMIT 'value']
  10. [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ];
  11. CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name
  12. ( { column_name type_name
  13. [ { [CONSTRAINT constraint_name] NULL |
  14. [CONSTRAINT constraint_name] NOT NULL |
  15. column_constraint [...]} ] |
  16. table_constraint} [, ...] )
  17. SERVER server_name
  18. OPTIONS ( { option_name ' value ' } [, ...] )
  19. DISTRIBUTE BY {ROUNDROBIN | REPLICATION}
  20. [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ]
  21. [ PARTITION BY ( column_name ) [AUTOMAPPED]] ;
  22. CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name
  23. ( [ { column_name type_name | LIKE source_table } [, ...] ] )
  24. SERVER server_name
  25. OPTIONS ( { option_name ' value ' } [, ...] )
  26. [ READ ONLY ]
  27. [ DISTRIBUTE BY {ROUNDROBIN} ]
  28. [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ];
  29. where column_constraint can be:
  30. [CONSTRAINT constraint_name]
  31. {PRIMARY KEY | UNIQUE}
  32. [NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED]
  33. where table_constraint can be:
  34. [CONSTRAINT constraint_name]
  35. {PRIMARY KEY | UNIQUE} (column_name)
  36. [NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED]

CREATE FUNCTION

创建一个函数。

  1. CREATE [ OR REPLACE ] FUNCTION function_name
  2. ( [ { argname [ argmode ] argtype [ { DEFAULT | := | = } expression ]} [, ...] ] )
  3. [ RETURNS rettype [ DETERMINISTIC ]
  4. | RETURNS TABLE ( { column_name column_type } [, ...] )]
  5. LANGUAGE lang_name
  6. [
  7. {IMMUTABLE | STABLE | VOLATILE}
  8. | {SHIPPABLE | NOT SHIPPABLE}
  9. | [ NOT ] LEAKPROOF
  10. | WINDOW
  11. | {CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT}
  12. | {[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | AUTHID DEFINER | AUTHID CURRENT_USER}
  13. | {FENCED | NOT FENCED}
  14. | {PACKAGE}
  15. | COST execution_cost
  16. | ROWS result_rows
  17. | SET configuration_parameter { {TO | =} value | FROM CURRENT }
  18. ] [...]
  19. {
  20. AS 'definition'
  21. | AS 'obj_file', 'link_symbol'
  22. }
  23. CREATE [ OR REPLACE ] FUNCTION function_name
  24. ( [ { argname [ argmode ] argtype [ { DEFAULT | := | = } expression ] } [, ...] ] )
  25. RETURN rettype [ DETERMINISTIC ]
  26. [
  27. {IMMUTABLE | STABLE | VOLATILE }
  28. | {SHIPPABLE | NOT SHIPPABLE}
  29. | {PACKAGE}
  30. | [ NOT ] LEAKPROOF
  31. | {CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
  32. | {[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | | AUTHID DEFINER | AUTHID CURRENT_USER}
  33. | COST execution_cost
  34. | ROWS result_rows
  35. | SET configuration_parameter { {TO | =} value | FROM CURRENT }
  36. ][...]
  37. {
  38. IS | AS
  39. } plsql_body
  40. /

CREATE GROUP

创建一个新用户组。

  1. CREATE GROUP group_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' [ EXPIRED ] | DISABLE };
  2. where option can be:
  3. {SYSADMIN | NOSYSADMIN}
  4. | {MONADMIN | NOMONADMIN}
  5. | {OPRADMIN | NOOPRADMIN}
  6. | {POLADMIN | NOPOLADMIN}
  7. | {AUDITADMIN | NOAUDITADMIN}
  8. | {CREATEDB | NOCREATEDB}
  9. | {USEFT | NOUSEFT}
  10. | {CREATEROLE | NOCREATEROLE}
  11. | {INHERIT | NOINHERIT}
  12. | {LOGIN | NOLOGIN}
  13. | {REPLICATION | NOREPLICATION}
  14. | {INDEPENDENT | NOINDEPENDENT}
  15. | {VCADMIN | NOVCADMIN}
  16. | {PERSISTENCE | NOPERSISTENCE}
  17. | CONNECTION LIMIT connlimit
  18. | VALID BEGIN 'timestamp'
  19. | VALID UNTIL 'timestamp'
  20. | RESOURCE POOL 'respool'
  21. | USER GROUP 'groupuser'
  22. | PERM SPACE 'spacelimit'
  23. | TEMP SPACE 'tmpspacelimit'
  24. | SPILL SPACE 'spillspacelimit'
  25. | NODE GROUP logic_group_name
  26. | IN ROLE role_name [, ...]
  27. | IN GROUP role_name [, ...]
  28. | ROLE role_name [, ...]
  29. | ADMIN role_name [, ...]
  30. | USER role_name [, ...]
  31. | SYSID uid
  32. | DEFAULT TABLESPACE tablespace_name
  33. | PROFILE DEFAULT
  34. | PROFILE profile_name
  35. | PGUSER

CREATE INDEX

在指定的表上创建索引。

  1. CREATE [ UNIQUE ] INDEX [ [schema_name.] index_name ] ON table_name [ USING method ]
  2. ({ { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, ...] )
  3. [ WITH ( {storage_parameter = value} [, ... ] ) ]
  4. [ TABLESPACE tablespace_name ]
  5. [ WHERE predicate ];
  6. CREATE [ UNIQUE ] INDEX [ [schema_name.] index_name ] ON table_name [ USING method ]
  7. ( {{ column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] }[, ...] )
  8. [ LOCAL [ ( { PARTITION index_partition_name [ TABLESPACE index_partition_tablespace ] } [, ...] ) ] | GLOBAL ]
  9. [ WITH ( { storage_parameter = value } [, ...] ) ]
  10. [ TABLESPACE tablespace_name ];

CREATE LANGUAGE

定义一种新的过程语言。单机和集中式暂不支持创建过程语言。

  1. CREATE [ UNIQUE ] INDEX [ [schema_name.] index_name ] ON table_name [ USING method ]
  2. ({ { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, ...] )
  3. [ WITH ( {storage_parameter = value} [, ... ] ) ]
  4. [ TABLESPACE tablespace_name ]
  5. [ WHERE predicate ];
  6. CREATE [ UNIQUE ] INDEX [ [schema_name.] index_name ] ON table_name [ USING method ]
  7. ( {{ column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] }[, ...] )
  8. [ LOCAL [ ( { PARTITION index_partition_name [ TABLESPACE index_partition_tablespace ] } [, ...] ) ] | GLOBAL ]
  9. [ WITH ( { storage_parameter = value } [, ...] ) ]
  10. [ TABLESPACE tablespace_name ];
  11. openGauss=# \h CREATE LANGUAGE
  12. Command: CREATE LANGUAGE
  13. Description: define a new procedural language
  14. Syntax:
  15. CREATE [ OR REPLACE ] [ PROCEDURAL ] LANGUAGE name;
  16. CREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
  17. HANDLER call_handler [ INLINE inline_handler ] [ VALIDATOR valfunction ];

CREATE MASKING POLICY

创建脱敏策略。

  1. CREATE MASKING POLICY policy_name masking_clause [, ... ] [ policy_filter_clause ] [ ENABLE | DISABLE ];
  2. where masking_clause can be:
  3. masking_function ON LABEL(label_name [, ... ])
  4. where masking_function can be:
  5. { maskall | randommasking | creditcardmasking | basicemailmasking | fullemailmasking | shufflemasking | alldigitsmasking | regexpmasking }
  6. where policy_filter_clause can be:
  7. FILTER ON { ( FILTER_TYPE ( filter_value [, ... ] ) ) [, ... ] }
  8. where FILTER_TYPE can be:
  9. { APP | ROLES | IP }

CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW会创建一个全量物化视图,并且后续可以使用REFRESH MATERIALIZED VIEW(全量刷新)刷新物化视图的数据。

  1. CREATE [ INCREMENTAL ] MATERIALIZED VIEW table_name
  2. [ (column_name [, ...] ) ]
  3. [ TABLESPACE tablespace_name ]
  4. AS query

CREATE MODEL

训练机器学习模型并保存模型。

  1. CREATE MODEL model_name USING algorithm_name
  2. [FEATURES { {expression [ [ AS ] output_name ]} [, ...] }]
  3. [TARGET { {expression [ [ AS ] output_name ]} [, ...] }]
  4. FROM { table_name | select_query }
  5. WITH hyperparameter_name = { hyperparameter_value | DEFAULT } [, ...] }

CREATE OPERATOR

定义一个新操作符。

  1. CREATE OPERATOR name (
  2. PROCEDURE = function_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. )

CREATE PACKAGE

创建一个新的PACKAGE。

  1. CREATE [ OR REPLACE ] PACKAGE [ schema ] package_name
  2. [ invoker_rights_clause ] { IS | AS } item_list_1 END package_name;

CREATE PROCEDURE

创建一个新的存储过程。

  1. CREATE [ OR REPLACE ] PACKAGE [ schema ] package_name
  2. [ invoker_rights_clause ] { IS | AS } item_list_1 END package_name;
  3. openGauss=# \h CREATE PROCEDURE
  4. Command: CREATE PROCEDURE
  5. Description: create a procedure
  6. Syntax:
  7. CREATE [ OR REPLACE ] PROCEDURE procedure_name
  8. [ ( {[ argmode ] [ argname ] argtype [ { DEFAULT | := | = } expression ]}[,...]) ]
  9. { IS | AS } plsql_body
  10. /

CREATE RESOURCE LABEL

创建资源标签。

  1. CREATE RESOURCE LABEL [ IF NOT EXISTS ] label_name ADD label_item_list[ , ... ];
  2. where label_item_list can be:
  3. resource_type(resource_path[, ... ])
  4. where resource_type can be:
  5. { TABLE | COLUMN | SCHEMA | VIEW | FUNCTION }

CREATE RESOURCE POOL

创建一个资源池,并指定此资源池相关联的控制组。

  1. CREATE RESOURCE POOL pool_name
  2. [WITH ({MEM_PERCENT=pct | CONTROL_GROUP="group_name" | ACTIVE_STATEMENTS=stmt | MAX_DOP = dop | MEMORY_LIMIT='memory_size' | io_limits=io_limits | io_priority='priority' | nodegroup='nodegroup_name' | is_foreign = boolean }[, ... ])];

CREATE ROLE

创建角色。

  1. CREATE ROLE role_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' [ EXPIRED ] | DISABLE };
  2. where option can be:
  3. {SYSADMIN | NOSYSADMIN}
  4. | {MONADMIN | NOMONADMIN}
  5. | {OPRADMIN | NOOPRADMIN}
  6. | {POLADMIN | NOPOLADMIN}
  7. | {AUDITADMIN | NOAUDITADMIN}
  8. | {CREATEDB | NOCREATEDB}
  9. | {USEFT | NOUSEFT}
  10. | {CREATEROLE | NOCREATEROLE}
  11. | {INHERIT | NOINHERIT}
  12. | {LOGIN | NOLOGIN}
  13. | {REPLICATION | NOREPLICATION}
  14. | {INDEPENDENT | NOINDEPENDENT}
  15. | {VCADMIN | NOVCADMIN}
  16. | {PERSISTENCE | NOPERSISTENCE}
  17. | CONNECTION LIMIT connlimit
  18. | VALID BEGIN 'timestamp'
  19. | VALID UNTIL 'timestamp'
  20. | RESOURCE POOL 'respool'
  21. | USER GROUP 'groupuser'
  22. | PERM SPACE 'spacelimit'
  23. | TEMP SPACE 'tmpspacelimit'
  24. | SPILL SPACE 'spillspacelimit'
  25. | NODE GROUP logic_cluster_name
  26. | IN ROLE role_name [, ...]
  27. | IN GROUP role_name [, ...]
  28. | ROLE role_name [, ...]
  29. | ADMIN role_name [, ...]
  30. | USER role_name [, ...]
  31. | SYSID uid
  32. | DEFAULT TABLESPACE tablespace_name
  33. | PROFILE DEFAULT
  34. | PROFILE profile_name
  35. | PGUSER

CREATE ROW LEVEL SECURITY POLICY

对表创建行访问控制策略。

  1. CREATE [ ROW LEVEL SECURITY ] POLICY policy_name ON table_name
  2. [ AS { PERMISSIVE | RESTRICTIVE } ]
  3. [ FOR { ALL | SELECT | UPDATE | DELETE } ]
  4. [ TO { role_name | PUBLIC } [, ...] ]
  5. USING ( using_expression )

CREATE SCHEMA

创建模式。

  1. CREATE SCHEMA schema_name
  2. [ AUTHORIZATION user_name ] [WITH BLOCKCHAIN] [ schema_element [ ... ] ];

CREATE SEQUENCE

用于向当前数据库里增加一个新的序列。序列的Owner为创建此序列的用户。

  1. CREATE SEQUENCE name [ INCREMENT [ BY ] increment ]
  2. [ MINVALUE minvalue | NO MINVALUE | NOMINVALUE] [ MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE]
  3. [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE | NOCYCLE]
  4. [ OWNED BY { table_name.column_name | NONE } ];

CREATE SERVER

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

  1. CREATE SERVER server_name
  2. FOREIGN DATA WRAPPER fdw_name
  3. OPTIONS ( { option_name ' value ' } [, ...] ) ;

CREATE SYNONYM

创建一个同义词对象。同义词是数据库对象的别名,用于记录与其他数据库对象名间的映射关系,用户可以使用同义词访问关联的数据库对象。

  1. CREATE [ OR REPLACE ] SYNONYM synonym_name
  2. FOR object_name;

CREATE TABLE

在当前数据库中创建一个新的空白表,该表由命令执行者所有。

  1. CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
  2. ({ column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ] [encrypted with ('column_encryption_key', 'encryption_type')]
  3. | table_constraint
  4. | LIKE source_table [ like_option [...] ] }
  5. [, ... ])
  6. [ WITH ( {storage_parameter = value} [, ... ] ) ]
  7. [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
  8. [ COMPRESS | NOCOMPRESS ]
  9. [ TABLESPACE tablespace_name ];
  10. where column_constraint can be:
  11. [ CONSTRAINT constraint_name ]
  12. { NOT NULL |
  13. NULL |
  14. CHECK ( expression ) |
  15. DEFAULT default_expr |
  16. GENERATED ALWAYS AS ( generation_expr ) STORED |
  17. UNIQUE index_parameters |
  18. PRIMARY KEY index_parameters |
  19. ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = column_encryption_key, ENCRYPTION_TYPE = encryption_type_value ) |
  20. REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
  21. [ ON DELETE action ] [ ON UPDATE action ] }
  22. [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  23. where table_constraint can be:
  24. [ CONSTRAINT constraint_name ]
  25. { CHECK ( expression ) |
  26. UNIQUE ( column_name [, ... ] ) index_parameters |
  27. PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  28. PARTIAL CLUSTER KEY ( column_name [, ... ] ) |
  29. FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
  30. [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
  31. [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  32. where compress_mode can be:
  33. { DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS }
  34. where like_option can be:
  35. { INCLUDING | EXCLUDING } { DEFAULTS | GENERATED | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | PARTITION | RELOPTIONS | DISTRIBUTION | ALL }
  36. where index_parameters can be:
  37. [ WITH ( {storage_parameter = value} [, ... ] ) ]
  38. [ USING INDEX TABLESPACE tablespace_name ]

CREATE TABLE AS

根据查询结果创建表。

  1. CREATE [ UNLOGGED ] TABLE table_name
  2. [ (column_name [, ...] ) ]
  3. [ WITH ( {storage_parameter = value} [, ... ] ) ]
  4. [ COMPRESS | NOCOMPRESS ]
  5. [ TABLESPACE tablespace_name ]
  6. [ DISTRIBUTE BY { REPLICATION | { [HASH ] ( column_name ) } } ]
  7. [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ]
  8. AS query
  9. [ WITH [ NO ] DATA ];

CREATE TABLE PARTITION

创建分区表。分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储,这张逻辑上的表称之为分区表,物理块称之为分区。分区表是一张逻辑表,不存储数据,数据实际是存储在分区上的。

  1. CREATE TABLE [ IF NOT EXISTS ] partition_table_name
  2. ( [
  3. { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
  4. | table_constraint
  5. | LIKE source_table [ like_option [...] ] }
  6. [, ... ]
  7. ] )
  8. [ WITH ( {storage_parameter = value} [, ... ] ) ]
  9. [ COMPRESS | NOCOMPRESS ]
  10. [ TABLESPACE tablespace_name ]
  11. [ DISTRIBUTE BY { REPLICATION | { [ HASH ] ( column_name ) } } ]
  12. [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ]
  13. PARTITION BY {
  14. {VALUES (partition_key)} |
  15. {RANGE (partition_key) [ INTERVAL ('interval_expr') [ STORE IN ( tablespace_name [, ... ] ) ] ] ( partition_less_than_item [, ... ] )} |
  16. {RANGE (partition_key) [ INTERVAL ('interval_expr') [ STORE IN ( tablespace_name [, ... ] ) ] ] ( partition_start_end_item [, ... ] )} |
  17. {LIST | HASH (partition_key) (PARTITION partition_name [VALUES (list_values_clause)] opt_table_space )}
  18. NOTICE: LIST/HASH partition is only avaliable in CENTRALIZED mode!
  19. } [ { ENABLE | DISABLE } ROW MOVEMENT ];
  20. where column_constraint can be:
  21. [ CONSTRAINT constraint_name ]
  22. { NOT NULL |
  23. NULL |
  24. CHECK ( expression ) |
  25. DEFAULT default_expr |
  26. GENERATED ALWAYS AS ( generation_expr ) STORED |
  27. UNIQUE index_parameters |
  28. PRIMARY KEY index_parameters |
  29. REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
  30. [ ON DELETE action ] [ ON UPDATE action ] }
  31. [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  32. where table_constraint can be:
  33. [ CONSTRAINT constraint_name ]
  34. { CHECK ( expression ) |
  35. UNIQUE ( column_name [, ... ] ) index_parameters |
  36. PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  37. FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
  38. [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
  39. [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  40. where index_parameters can be:
  41. [ WITH ( {storage_parameter = value} [, ... ] ) ]
  42. [ USING INDEX TABLESPACE tablespace_name ]
  43. where like_option can be:
  44. { INCLUDING | EXCLUDING } { DEFAULTS | GENERATED | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | RELOPTIONS | DISTRIBUTION | ALL }
  45. where partition_less_than_item can be:
  46. PARTITION partition_name VALUES LESS THAN ( { partition_value | MAXVALUE } ) [TABLESPACE tablespace_name]
  47. where partition_start_end_item can be:
  48. PARTITION partition_name {
  49. {START(partition_value) END (partition_value) EVERY (interval_value)} |
  50. {START(partition_value) END ({partition_value | MAXVALUE})} |
  51. {START(partition_value)} |
  52. {END({partition_value | MAXVALUE})}
  53. } [TABLESPACE tablespace_name]

CREATE TABLESPACE

在数据库中创建一个新的表空间。

  1. CREATE TABLESPACE tablespace_name
  2. [ OWNER user_name ] [ RELATIVE ] LOCATION 'directory' [ MAXSIZE 'space_size' ]
  3. [with_option_clause];
  4. where option_clause can be:
  5. WITH ( filesystem= { 'systemtype '| " systemtype " | systemtype }
  6. [ { , address = { ' ip:port [ , ... ] ' | " ip:port [ , ... ] "} } ]
  7. , cfgpath = { 'path '| " path " } ,storepath = { 'rootpath '| " rootpath "}
  8. [{, random_page_cost = { 'value '| " value " | value }}]
  9. [{,seq_page_cost = { 'value '| " value " | value }}])

CREATE TEXT SEARCH CONFIGURATION

创建新的文本搜索配置。一个文本搜索配置声明一个能将一个字符串划分成符号的文本搜索解析器,加上可以用于确定搜索对哪些标记感兴趣的字典

  1. CREATE TEXT SEARCH CONFIGURATION name (
  2. PARSER = parser_name |
  3. COPY = source_config
  4. ) [ WITH ( {configuration_option = value} [, ...] )];

CREATE TEXT SEARCH DICTIONARY

删除全文检索词典。

  1. CREATE TEXT SEARCH DICTIONARY name
  2. ( TEMPLATE = template_name | COPY = source_config
  3. [, option = value [, ...] ] );

CREATE TRIGGER

创建一个触发器。 触发器将与指定的表或视图关联,并在特定条件下执行指定的函数。

  1. CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
  2. ON table_name
  3. [ FROM referenced_table_name ]
  4. { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
  5. [ FOR [ EACH ] { ROW | STATEMENT } ]
  6. [ WHEN ( condition ) ]
  7. EXECUTE PROCEDURE function_name ( arguments )
  8. where event can be one of:
  9. INSERT
  10. UPDATE [ OF column_name [, ... ] ]
  11. DELETE
  12. TRUNCATE

CREATE TYPE

在当前数据库中定义一种新的数据类型。定义数据类型的用户将成为该数据类型的拥有者。类型只适用于行存表

  1. CREATE TYPE name AS
  2. ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] )
  3. CREATE TYPE name AS ENUM
  4. ( [ 'label' [, ... ] ] )
  5. CREATE TYPE name (
  6. INPUT = input_function,
  7. OUTPUT = output_function
  8. [ , RECEIVE = receive_function ]
  9. [ , SEND = send_function ]
  10. [ , TYPMOD_IN = type_modifier_input_function ]
  11. [ , TYPMOD_OUT = type_modifier_output_function ]
  12. [ , ANALYZE = analyze_function ]
  13. [ , INTERNALLENGTH = { internallength | VARIABLE } ]
  14. [ , PASSEDBYVALUE ]
  15. [ , ALIGNMENT = alignment ]
  16. [ , STORAGE = storage ]
  17. [ , LIKE = like_type ]
  18. [ , CATEGORY = category ]
  19. [ , PREFERRED = preferred ]
  20. [ , DEFAULT = default ]
  21. [ , ELEMENT = element ]
  22. [ , DELIMITER = delimiter ]
  23. [ , COLLATABLE = collatable ]
  24. )
  25. CREATE TYPE name

CREATE USER

创建一个用户。

  1. CREATE USER user_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' [ EXPIRED ] | DISABLE };
  2. where option can be:
  3. {SYSADMIN | NOSYSADMIN}
  4. | {MONADMIN | NOMONADMIN}
  5. | {OPRADMIN | NOOPRADMIN}
  6. | {POLADMIN | NOPOLADMIN}
  7. | {AUDITADMIN | NOAUDITADMIN}
  8. | {CREATEDB | NOCREATEDB}
  9. | {USEFT | NOUSEFT}
  10. | {CREATEROLE | NOCREATEROLE}
  11. | {INHERIT | NOINHERIT}
  12. | {LOGIN | NOLOGIN}
  13. | {REPLICATION | NOREPLICATION}
  14. | {INDEPENDENT | NOINDEPENDENT}
  15. | {VCADMIN | NOVCADMIN}
  16. | {PERSISTENCE | NOPERSISTENCE}
  17. | CONNECTION LIMIT connlimit
  18. | VALID BEGIN 'timestamp'
  19. | VALID UNTIL 'timestamp'
  20. | RESOURCE POOL 'respool'
  21. | USER GROUP 'groupuser'
  22. | PERM SPACE 'spacelimit'
  23. | TEMP SPACE 'tmpspacelimit'
  24. | SPILL SPACE 'spillspacelimit'
  25. | NODE GROUP logic_cluster_name
  26. | IN ROLE role_name [, ...]
  27. | IN GROUP role_name [, ...]
  28. | ROLE role_name [, ...]
  29. | ADMIN role_name [, ...]
  30. | USER role_name [, ...]
  31. | SYSID uid
  32. | DEFAULT TABLESPACE tablespace_name
  33. | PROFILE DEFAULT
  34. | PROFILE profile_name
  35. | PGUSER

CREATE VIEW

创建一个视图。

  1. CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW view_name [ ( column_name [, ...] ) ]
  2. [ WITH ( {view_option_name [= view_option_value]} [, ... ] ) ]
  3. AS query;

CREATE WEAK PASSWORD DICTIONARY

向gs_global_config表中插入一个或者多个弱口令。

  1. CREATE WEAK PASSWORD DICTIONARY
  2. [WITH VALUES] ( {'weak_password'} [, ...] );

CURSOR

定义一个游标,用于在一个大的查询里面检索少数几行数据。

  1. CURSOR cursor_name
  2. [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
  3. FOR query ;

DEALLOCATE

用于删除前面编写的预备语句。如果用户没有明确删除一个预备语句,那么它将在会话结束的时候被删除。

  1. DEALLOCATE [ PREPARE ] { name | ALL };

DECLARE

用于删除前面编写的预备语句。如果用户没有明确删除一个预备语句,那么它将在会话结束的时候被删除。

  1. 1. declare a cursor:
  2. DECLARE cursor_name [ BINARY ] [ NO SCROLL ]
  3. CURSOR [ { WITH | WITHOUT } HOLD ] FOR query ;
  4. 2. start an anonymous block:
  5. [DECLARE [declare_statements]]
  6. BEGIN
  7. execution_statements
  8. END;
  9. /

DELETE

从指定的表里删除满足WHERE子句的行。如果WHERE子句不存在,将删除表中所有行,结果只保留表结构。

  1. [ WITH [ RECURSIVE ] with_query [, ...] ]
  2. DELETE [/*+ plan_hint */] FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
  3. [ USING using_list ]
  4. [ WHERE condition | WHERE CURRENT OF cursor_name ] [ LIMIT row_count ]
  5. [ RETURNING { * | { output_expr [ [ AS ] output_name ] } [, ...] } ];

DO

执行匿名代码块。

  1. DO [ LANGUAGE lang_name ] code;

DROP AUDIT POLICY

删除一个审计策略。

  1. DROP AUDIT POLICY [IF EXISTS] policy_name;

DROP CLIENT MASTER KEY

删除一个客户端加密主密钥(CMK)。

  1. DROP CLIENT MASTER KEY [ IF EXISTS ] client_master_key_name [, ...];

DROP COLUMN ENCRYPTION KEY

删除一个列加密密钥(cek)。

  1. DROP COLUMN ENCRYPTION KEY [ IF EXISTS ] client_column_key_name [, ...];

DROP DATA SOURCE

删除一个Data Source对象。

  1. DROP DATA SOURCE [IF EXISTS] src_name [CASCADE | RESTRICT];

DROP DATABASE

删除一个数据库。

  1. DROP DATABASE [ IF EXISTS ] database_name;

DROP DIRECTORY

删除指定的directory对象。

  1. DROP DIRECTORY [ IF EXISTS ] directory_name;

DROP EXTENSION

删除一个扩展。

  1. DROP EXTENSION [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ];

DROP FOREIGN TABLE

删除指定的外表。

  1. DROP FOREIGN TABLE [ IF EXISTS ]
  2. table_name [, ...] [ CASCADE | RESTRICT ];

DROP FUNCTION

删除一个已存在的函数。

  1. DROP FUNCTION [ IF EXISTS ] function_name [ ( [ {[ argmode ] [ argname ] argtype} [, ...] ] ) [ CASCADE | RESTRICT ] ];

DROP GROUP

删除用户组。

  1. DROP GROUP [ IF EXISTS ] group_name [, ...];

DROP INDEX

删除索引。

  1. DROP INDEX [ IF EXISTS ]
  2. index_name [, ...] [ CASCADE | RESTRICT ];

DROP MASKING POLICY

删除脱敏策略。

  1. DROP MASKING POLICY [IF EXISTS] policy_name;

DROP MATERIALIZED VIEW

强制删除数据库中已有的物化视图。

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

DROP MODEL

删除一个已训练完成保存的模型对象。

  1. DROP MODEL model_name;

DROP OPERATOR

openGauss暂不支持。

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

DROP OWNED

删除一个数据库角色所拥有的数据库对象。

  1. DROP OWNED BY name [, ...] [ CASCADE | RESTRICT ];

DROP PACKAGE

删除已存在的PACKAGE或者PACKAGE BODY。

  1. DROP PACKAGE [ IF EXISTS ] package_name;

DROP PROCEDURE

删除已存在的存储过程。

  1. DROP PROCEDURE [ IF EXISTS ] procedure_name;

DROP RESOURCE LABEL

删除资源标签。

  1. DROP RESOURCE LABEL [ IF EXISTS ] policy_name[, ... ];

DROP RESOURCE POOL

删除一个资源池。

  1. DROP RESOURCE POOL [ IF EXISTS ] pool_name;

DROP ROLE

删除指定的角色。

  1. DROP ROLE [ IF EXISTS ] role_name [, ...];

DROP ROW LEVEL SECURITY POLICY

删除表上某个行访问控制策略。

  1. DROP [ ROW LEVEL SECURITY ] POLICY [ IF EXISTS ] policy_name ON table_name [ CASCADE | RESTRICT ]

DROP SCHEMA

从数据库中删除模式。

  1. DROP SCHEMA [ IF EXISTS ] schema_name [, ...] [ CASCADE | RESTRICT ];

DROP SEQUENCE

从当前数据库里删除序列。

  1. DROP SEQUENCE [ IF EXISTS ] {[schema.]sequence_name} [, ...] [ CASCADE | RESTRICT ];

DROP SERVER

删除现有的一个数据服务器。

  1. DROP SERVER [ IF EXISTS ] server_name [ { CASCADE | RESTRICT } ] ;

DROP SYNONYM

删除指定的SYNONYM对象。

  1. DROP SYNONYM [ IF EXISTS ] synonym_name [ CASCADE | RESTRICT ];

DROP TABLE

删除指定的表。

  1. DROP TABLE [ IF EXISTS ]
  2. {[schema.]table_name} [, ...] [ CASCADE | RESTRICT ];

DROP TABLESPACE

删除一个表空间。

  1. DROP TABLESPACE [ IF EXISTS ] tablespace_name;

DROP TEXT SEARCH CONFIGURATION

删除已有文本搜索配置。

  1. DROP TEXT SEARCH CONFIGURATION [ IF EXISTS ] name [ CASCADE | RESTRICT ]

DROP TEXT SEARCH DICTIONARY

删除全文检索词典。

  1. DROP TEXT SEARCH DICTIONARY [ IF EXISTS ] name [ CASCADE | RESTRICT ];

DROP TRIGGER

删除触发器。

  1. DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]

DROP TYPE

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

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

DROP USER

删除用户,同时会删除同名的schema。

  1. DROP USER [ IF EXISTS ] user_name [, ...] [ CASCADE | RESTRICT ];

DROP VIEW

数据库中强制删除已有的视图。

  1. DROP VIEW [ IF EXISTS ] view_name [, ...] [ CASCADE | RESTRICT ];

DROP WEAK PASSWORD DICTIONARY

清空gs_global_config中的所有弱口令。

  1. DROP WEAK PASSWORD DICTIONARY;

END

END可完成提交事务的功能,即提交事务的所有操作。

  1. END [ WORK | TRANSACTION ]

EXECUTE

执行一个前面准备好的预备语句。因为一个预备语句只在会话的生命期里存在,那么预备语句必须是在当前会话的前些时候用PREPARE语句创建的。

  1. EXECUTE name [ ( parameter [, ...] ) ];

EXECUTE DIRECT

在指定的节点上执行SQL语句。一般情况下,SQL语句的执行是由集群负载自动分配到合适的节点上,execute direct主要用于数据库维护和测试。

  1. EXPLAIN [ ( option [, ...] ) ] statement;
  2. EXPLAIN { [ { ANALYZE | ANALYSE } ] [ VERBOSE ] | PERFORMANCE } statement;
  3. where option can be:
  4. ANALYZE [ boolean ] |
  5. ANALYSE [ boolean ] |
  6. VERBOSE [ boolean ] |
  7. COSTS [ boolean ] |
  8. CPU [ boolean ] |
  9. DETAIL [ boolean ] |
  10. NODES [ boolean ] |
  11. NUM_NODES [ boolean ] |
  12. BUFFERS [ boolean ] |
  13. TIMING [ boolean ] |
  14. PLAN [ boolean ] |
  15. FORMAT { TEXT | XML | JSON | YAML }
  16. openGauss=# \h EXECUTE DIRECT
  17. Command: EXECUTE DIRECT
  18. Description: launch queries directly to dedicated nodes
  19. Syntax:
  20. EXECUTE DIRECT ON ( nodename [, ... ] ) query;
  21. EXECUTE DIRECT ON { COORDINATORS | DATANODES | ALL } query;

EXPLAIN

显示SQL语句的执行计划。

  1. EXPLAIN [ ( option [, ...] ) ] statement;
  2. EXPLAIN { [ { ANALYZE | ANALYSE } ] [ VERBOSE ] | PERFORMANCE } statement;
  3. where option can be:
  4. ANALYZE [ boolean ] |
  5. ANALYSE [ boolean ] |
  6. VERBOSE [ boolean ] |
  7. COSTS [ boolean ] |
  8. CPU [ boolean ] |
  9. DETAIL [ boolean ] |
  10. NODES [ boolean ] |
  11. NUM_NODES [ boolean ] |
  12. BUFFERS [ boolean ] |
  13. TIMING [ boolean ] |
  14. PLAN [ boolean ] |
  15. FORMAT { TEXT | XML | JSON | YAML }

FETCH

FETCH通过已创建的游标来检索数据。

  1. FETCH [ direction { FROM | IN } ] cursor_name;
  2. where direction can be:
  3. NEXT
  4. | PRIOR
  5. | FIRST
  6. | LAST
  7. | ABSOLUTE count
  8. | RELATIVE count
  9. | count
  10. | ALL
  11. | FORWARD
  12. | FORWARD count
  13. | FORWARD ALL
  14. | BACKWARD
  15. | BACKWARD count
  16. | BACKWARD ALL

GRANT

对角色和用户进行授权操作。

  1. GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES |
  2. ALTER | DROP | COMMENT | INDEX | VACUUM } [, ...] | ALL [ PRIVILEGES ] }
  3. ON { [ TABLE ] table_name [, ...]
  4. | ALL TABLES IN SCHEMA schema_name [, ...] }
  5. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  6. [ WITH GRANT OPTION ];
  7. GRANT { {{ SELECT | INSERT | UPDATE | REFERENCES | COMMENT } ( column_name [, ...] )}
  8. [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
  9. ON [ TABLE ] table_name [, ...]
  10. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  11. [ WITH GRANT OPTION ];
  12. GRANT { { SELECT | UPDATE | USAGE | ALTER | DROP | COMMENT } [, ...]
  13. | ALL [ PRIVILEGES ] }
  14. ON { [ SEQUENCE ] sequence_name [, ...]
  15. | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
  16. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  17. [ WITH GRANT OPTION ];
  18. GRANT { { CREATE | CONNECT | TEMPORARY | TEMP | ALTER | DROP | COMMENT }
  19. [, ...] | ALL [ PRIVILEGES ] }
  20. ON DATABASE database_name [, ...]
  21. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  22. [ WITH GRANT OPTION ];
  23. GRANT { USAGE | ALL [ PRIVILEGES ] }
  24. ON DOMAIN domain_name [, ...]
  25. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  26. [ WITH GRANT OPTION ];
  27. GRANT { { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
  28. ON CLIENT_MASTER_KEY client_master_key
  29. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  30. [ WITH GRANT OPTION ];
  31. GRANT { { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
  32. ON COLUMN_ENCRYPTION_KEY column_encryption_key
  33. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  34. [ WITH GRANT OPTION ];
  35. GRANT { USAGE | ALL [ PRIVILEGES ] }
  36. ON FOREIGN DATA WRAPPER fdw_name [, ...]
  37. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  38. [ WITH GRANT OPTION ];
  39. GRANT { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
  40. ON FOREIGN SERVER server_name [, ...]
  41. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  42. [ WITH GRANT OPTION ];
  43. GRANT { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
  44. ON { FUNCTION {function_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...]
  45. | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
  46. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  47. [ WITH GRANT OPTION ];
  48. GRANT { USAGE | ALL [ PRIVILEGES ] }
  49. ON LANGUAGE lang_name [, ...]
  50. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  51. [ WITH GRANT OPTION ];
  52. GRANT { { CREATE | USAGE | COMPUTE | ALTER | DROP } [, ...] | ALL [ PRIVILEGES ] }
  53. ON NODE GROUP group_name [, ...]
  54. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  55. [ WITH GRANT OPTION ];
  56. GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
  57. ON LARGE OBJECT loid [, ...]
  58. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  59. [ WITH GRANT OPTION ];
  60. GRANT { { CREATE | USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
  61. ON SCHEMA schema_name [, ...]
  62. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  63. [ WITH GRANT OPTION ];
  64. GRANT { { CREATE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
  65. ON TABLESPACE tablespace_name [, ...]
  66. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  67. [ WITH GRANT OPTION ];
  68. GRANT { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
  69. ON TYPE type_name [, ...]
  70. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  71. [ WITH GRANT OPTION ];
  72. GRANT { USAGE | ALL [PRIVILEGES] }
  73. ON DATA SOURCE src_name [, ...]
  74. TO { [GROUP] role_name | PUBLIC } [, ...]
  75. [WITH GRANT OPTION];
  76. GRANT { { READ | WRITE } [, ...] | ALL [PRIVILEGES] }
  77. ON DIRECTORY directory_name [, ...]
  78. TO { [GROUP] role_name | PUBLIC } [, ...]
  79. [WITH GRANT OPTION];
  80. GRANT { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
  81. ON PACKAGE package_name [, ...]
  82. TO { [ GROUP ] role_name | PUBLIC } [, ...]
  83. [ WITH GRANT OPTION ];
  84. GRANT role_name [, ...]
  85. TO role_name [, ...]
  86. [ WITH ADMIN OPTION ];
  87. GRANT ALL { PRIVILEGES | PRIVILEGE }
  88. TO role_name;

INSERT

向表中添加一行或多行数据。

  1. [ WITH [ RECURSIVE ] with_query [, ...] ]
  2. INSERT [/*+ plan_hint */] INTO table_name [ ( column_name [, ...] ) ]
  3. { DEFAULT VALUES | VALUES {( { expression | DEFAULT } [, ...] ) }[, ...] | query }
  4. [ ON DUPLICATE KEY UPDATE { NOTHING | { column_name = { expression | DEFAULT } } [, ...] } ]
  5. [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];

LOCK

LOCK TABLE获取表级锁。

  1. LOCK [ TABLE ] {[ ONLY ] name [, ...]| {name [ * ]} [, ...]}
  2. [ IN {ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE} MODE ]
  3. [ NOWAIT ];

MERGE INTO

将目标表和源表中数据针对关联条件进行匹配,若关联条件匹配时对目标表进行UPDATE,无法匹配时对目标表执行INSERT。此语法可以很方便地用来合并执行UPDATE和INSERT,避免多次执行。

  1. MERGE [/*+ plan_hint */] INTO table_name [ [ AS ] alias ]
  2. USING { { table_name | view_name } | subquery } [ [ AS ] alias ]
  3. ON ( condition )
  4. [
  5. WHEN MATCHED THEN
  6. UPDATE SET { column_name = { expression | DEFAULT } |
  7. ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
  8. [ WHERE condition ]
  9. ]
  10. [
  11. WHEN NOT MATCHED THEN
  12. INSERT { DEFAULT VALUES |
  13. [ ( column_name [, ...] ) ] VALUES ( { expression | DEFAULT } [, ...] ) [, ...] [ WHERE condition ] }
  14. ];

MOVE

MOVE在不检索数据的情况下重新定位一个游标。MOVE的作用类似于FETCH命令,但只是重定位游标而不返回行。

  1. MOVE [ direction [ FROM | IN ] ] cursor_name;
  2. where direction can be:
  3. NEXT
  4. | PRIOR
  5. | FIRST
  6. | LAST
  7. | ABSOLUTE count
  8. | RELATIVE count
  9. | count
  10. | ALL
  11. | FORWARD
  12. | FORWARD count
  13. | FORWARD ALL
  14. | BACKWARD
  15. | BACKWARD count
  16. | BACKWARD ALL

PREPARE

创建一个预备语句。

  1. PREPARE name [ ( data_type [, ...] ) ] AS statement;

PREPARE TRANSACTION

为当前事务做两阶段提交的准备。

  1. PREPARE TRANSACTION transaction_id;

REASSIGN OWNED

修改数据库对象的属主。

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

REFRESH MATERIALIZED VIEW

会以全量刷新的方式对物化视图进行刷新。

  1. REFRESH [ INCREMENTAL ] MATERIALIZED VIEW name

REINDEX

为表中的数据重建索引。

  1. REINDEX { INDEX | [INTERNAL] TABLE | DATABASE | SYSTEM } name [ FORCE ];
  2. REINDEX { INDEX | [INTERNAL] TABLE } name
  3. PARTITION partition_name [ FORCE ];

RESET

将指定的运行时参数恢复为缺省值。这些参数的缺省值是指postgresql.conf配置文件中所描述的参数缺省值。

  1. RESET {configuration_parameter | CURRENT_SCHEMA | TIME ZONE | TRANSACTION ISOLATION LEVEL | SESSION AUTHORIZATION | ALL };

REVOKE

用于撤销一个或多个角色的权限。

  1. REVOKE [ GRANT OPTION FOR ]
  2. { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES |
  3. ALTER | DROP | COMMENT | INDEX | VACUUM } [, ...] | ALL [ PRIVILEGES ] }
  4. ON { [ TABLE ] table_name [, ...]
  5. | ALL TABLES IN SCHEMA schema_name [, ...] }
  6. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  7. [ CASCADE | RESTRICT ];
  8. REVOKE [ GRANT OPTION FOR ]
  9. { {{ SELECT | INSERT | UPDATE | REFERENCES | COMMENT } ( column_name [, ...] )}
  10. [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
  11. ON [ TABLE ] table_name [, ...]
  12. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  13. [ CASCADE | RESTRICT ];
  14. REVOKE [ GRANT OPTION FOR ]
  15. { { SELECT | UPDATE | ALTER | DROP | COMMENT } [, ...]
  16. | ALL [ PRIVILEGES ] }
  17. ON { [ SEQUENCE ] sequence_name [, ...]
  18. | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
  19. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  20. [ CASCADE | RESTRICT ];
  21. REVOKE [ GRANT OPTION FOR ]
  22. { { CREATE | CONNECT | TEMPORARY | TEMP | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
  23. ON DATABASE database_name [, ...]
  24. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  25. [ CASCADE | RESTRICT ];
  26. REVOKE [ GRANT OPTION FOR ]
  27. { USAGE | ALL [ PRIVILEGES ] }
  28. ON DOMAIN domain_name [, ...]
  29. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  30. [ CASCADE | RESTRICT ];
  31. REVOKE [ GRANT OPTION FOR ]
  32. { { USAGE | DROP } [, ...] | ALL [PRIVILEGES] }
  33. ON CLIENT_MASTER_KEYS client_master_keys_name [, ...]
  34. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  35. [ CASCADE | RESTRICT ];
  36. REVOKE [ GRANT OPTION FOR ]
  37. { { USAGE | DROP } [, ...] | ALL [PRIVILEGES]}
  38. ON COLUMN_ENCRYPTION_KEYS column_encryption_keys_name [, ...]
  39. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  40. [ CASCADE | RESTRICT ];
  41. REVOKE [ GRANT OPTION FOR ]
  42. { { READ | WRITE } [, ...] | ALL [ PRIVILEGES ] }
  43. ON DIRECTORY directory_name [, ...]
  44. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  45. [ CASCADE | RESTRICT ];
  46. REVOKE [ GRANT OPTION FOR ]
  47. { USAGE | ALL [ PRIVILEGES ] }
  48. ON FOREIGN DATA WRAPPER fdw_name [, ...]
  49. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  50. [ CASCADE | RESTRICT ];
  51. REVOKE [ GRANT OPTION FOR ]
  52. { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
  53. ON FOREIGN SERVER server_name [, ...]
  54. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  55. [ CASCADE | RESTRICT ];
  56. REVOKE [ GRANT OPTION FOR ]
  57. { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
  58. ON { FUNCTION {function_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...]
  59. | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
  60. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  61. [ CASCADE | RESTRICT ];
  62. REVOKE [ GRANT OPTION FOR ]
  63. { USAGE | ALL [ PRIVILEGES ] }
  64. ON LANGUAGE lang_name [, ...]
  65. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  66. [ CASCADE | RESTRICT ];
  67. REVOKE [ GRANT OPTION FOR ]
  68. { {CREATE | USAGE | COMPUTE | ALTER | DROP } [, ...] | ALL [ PRIVILEGES ] }
  69. ON NODE GROUP group_name [, ...]
  70. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  71. [ CASCADE | RESTRICT ];
  72. REVOKE [ GRANT OPTION FOR ]
  73. { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
  74. ON LARGE OBJECT loid [, ...]
  75. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  76. [ CASCADE | RESTRICT ];
  77. REVOKE [ GRANT OPTION FOR ]
  78. { { CREATE | USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
  79. ON SCHEMA schema_name [, ...]
  80. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  81. [ CASCADE | RESTRICT ];
  82. REVOKE [ GRANT OPTION FOR ]
  83. { { CREATE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
  84. ON TABLESPACE tablespace_name [, ...]
  85. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  86. [ CASCADE | RESTRICT ];
  87. REVOKE [ GRANT OPTION FOR ]
  88. { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
  89. ON TYPE type_name [, ...]
  90. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  91. [ CASCADE | RESTRICT ];
  92. REVOKE [ GRANT OPTION FOR ]
  93. { USAGE | ALL [ PRIVILEGES ] }
  94. ON DATA SOURCE src_name [, ...]
  95. FROM { [GROUP] role_name | PUBLIC } [, ...];
  96. REVOKE [ GRANT OPTION FOR ]
  97. { { READ | WRITE } [, ...] | ALL [ PRIVILEGES ] }
  98. ON DIRECTORY directory_name [, ...]
  99. FROM { [GROUP] role_name | PUBLIC } [, ...];
  100. REVOKE [ GRANT OPTION FOR ]
  101. { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
  102. ON PACKAGE package_name [, ...]
  103. FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  104. [ CASCADE | RESTRICT ];
  105. REVOKE [ ADMIN OPTION FOR ]
  106. role_name [, ...] FROM role_name [, ...]
  107. [ CASCADE | RESTRICT ];
  108. REVOKE ALL { PRIVILEGES | PRIVILEGE } FROM role_name;

ROLLBACK

回滚当前事务并取消当前事务中的所有更新。

  1. ROLLBACK [ WORK | TRANSACTION ];

ROLLBACK PREPARED

为当前事务做两阶段提交的准备。

  1. ROLLBACK PREPARED transaction_id;

SAVEPOINT

用于在当前事务里建立一个新的保存点。

  1. SAVEPOINT savepoint_name;

SELECT

SELECT用于从表或视图中取出数据。

  1. [ WITH [ RECURSIVE ] with_query [, ...] ]
  2. SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
  3. { * | {expression [ [ AS ] output_name ]} [, ...] }
  4. [ FROM from_item [, ...] ]
  5. [ WHERE condition ]
  6. [ GROUP BY grouping_element [, ...] ]
  7. [ HAVING condition [, ...] ]
  8. [ WINDOW {window_name AS ( window_definition )} [, ...] ]
  9. [ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ]
  10. [ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
  11. [ LIMIT { [offset,] count | ALL } ]
  12. [ OFFSET start [ ROW | ROWS ] ]
  13. [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
  14. [ {FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ]} [...] ];
  15. TABLE { ONLY {(table_name)| table_name} | table_name [ * ]};
  16. where from_item can be:
  17. [ ONLY ] table_name [ * ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
  18. [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
  19. |( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
  20. |with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
  21. |function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
  22. |function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
  23. |from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]
  24. where grouping_element can be:
  25. ()
  26. |expression
  27. |( expression [, ...] )
  28. |ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
  29. |CUBE ( { expression | ( expression [, ...] ) } [, ...] )
  30. |GROUPING SETS ( grouping_element [, ...] )
  31. where with_query can be:
  32. with_query_name [ ( column_name [, ...] ) ] AS ( {select | values | insert | update | delete} )
  33. where partition_clause can be:
  34. PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) }
  35. where nlssort_expression_clause can be:
  36. NLSSORT ( column_name, ' NLS_SORT = { SCHINESE_PINYIN_M | generic_m_ci } ' )

SELECT INTO

SELECT INTO用于根据查询结果创建一个新表,并且将查询到的数据插入到新表中。

  1. [ WITH [ RECURSIVE ] with_query [, ...] ]
  2. SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
  3. { * | {expression [ [ AS ] output_name ]} [, ...] }
  4. INTO [ UNLOGGED ] [ TABLE ] new_table
  5. [ FROM from_item [, ...] ]
  6. [ WHERE condition ]
  7. [ GROUP BY expression [, ...] ]
  8. [ HAVING condition [, ...] ]
  9. [ WINDOW {window_name AS ( window_definition )} [, ...] ]
  10. [ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ]
  11. [ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
  12. [ LIMIT { count | ALL } ]
  13. [ OFFSET start [ ROW | ROWS ] ]
  14. [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
  15. [ {FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ]} [...] ];

SET

用于修改运行时配置参数。

  1. SET [ LOCAL | SESSION ]
  2. { {config_parameter { { TO | = } { value | DEFAULT } | FROM CURRENT }}};
  3. SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT };
  4. SET [ SESSION | LOCAL ] NAMES encoding_name;
  5. SET [ SESSION | LOCAL ]
  6. {CURRENT_SCHEMA { TO | = } { schema | DEFAULT }
  7. | SCHEMA 'schema'};
  8. SET [ SESSION | LOCAL ] XML OPTION { DOCUMENT | CONTENT };

SET CONSTRAINTS

设置当前事务检查行为的约束条件。

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

SET ROLE

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

  1. SET [ SESSION | LOCAL ] ROLE role_name PASSWORD 'password';
  2. RESET ROLE;

SET SESSION AUTHORIZATION

把当前会话里的会话用户标识和当前用户标识都设置为指定的用户。

  1. SET [ SESSION | LOCAL ] SESSION AUTHORIZATION role_name PASSWORD 'password';
  2. {SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT
  3. | RESET SESSION AUTHORIZATION};

SET TRANSACTION

SET CONSTRAINTS设置当前事务检查行为的约束条件。

  1. {SET [ LOCAL ] TRANSACTION|SET SESSION CHARACTERISTICS AS TRANSACTION}
  2. { ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED }
  3. | { READ WRITE | READ ONLY | SERIALIZABLE | REPEATABLE READ }
  4. } [, ...]
  5. SET TRANSACTION SNAPSHOT snapshot_id;

SHOW

SHOW将显示当前运行时参数的数值。

  1. SHOW { configuration_parameter | CURRENT_SCHEMA | TIME ZONE | TRANSACTION ISOLATION LEVEL | SESSION AUTHORIZATION | ALL };

START TRANSACTION

通过START TRANSACTION启动事务。如果声明了隔离级别、读写模式,那么新事务就使用这些特性,类似执行了SET TRANSACTION。

  1. START TRANSACTION
  2. [ { ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED }
  3. | { READ WRITE | READ ONLY | SERIALIZABLE | REPEATABLE READ }
  4. } [, ...] ];

TRUNCATE

清理表数据,TRUNCATE快速地从表中删除所有行。

  1. TRUNCATE [ TABLE ] [ ONLY ] {table_name [ * ]} [, ... ]
  2. [ CONTINUE IDENTITY ] [ CASCADE | RESTRICT ];
  3. ALTER TABLE [ IF EXISTS ] { [ ONLY ] table_name | table_name * | ONLY ( table_name ) }
  4. TRUNCATE PARTITION { partition_name | FOR ( partition_value [, ...] ) } ;

UPDATE

更新表中的数据。UPDATE修改满足条件的所有行中指定的字段值,WHERE子句声明条件,SET子句指定的字段会被修改,没有出现的字段则保持它们的原值。

  1. UPDATE [/*+ plan_hint */] [ ONLY ] table_name [ * ] [ [ AS ] alias ]
  2. SET {column_name = { expression | DEFAULT } |
  3. ( column_name [, ...] ) = {( { expression | DEFAULT } [, ...] ) |sub_query }
  4. }[, ...]
  5. [ FROM from_list] [ WHERE condition ]
  6. [ RETURNING {* | {output_expression [ [ AS ] output_name ]} [, ...] }];

VACUUM

VACUUM回收表或B-Tree索引中已经删除的行所占据的存储空间。在一般的数据库操作里,那些已经DELETE的行并没有从它们所属的表中物理删除;在完成VACUUM之前它们仍然存在。因此有必要周期地运行VACUUM,特别是在经常更新的表上。

  1. VACUUM [ ( { FULL | FREEZE | VERBOSE | {ANALYZE | ANALYSE }} [,...] ) ]
  2. [ table_name [ (column_name [, ...] ) ] ] [ PARTITION ( partition_name ) ];
  3. VACUUM [ FULL [ COMPACT ] ] [ FREEZE ] [ VERBOSE ] [ table_name ] [ PARTITION ( partition_name ) ];
  4. VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] { ANALYZE | ANALYSE } [ VERBOSE ]
  5. [ table_name [ (column_name [, ...] ) ] ] [ PARTITION ( partition_name ) ];
  6. VACUUM DELTAMERGE [ table_name ];
  7. VACUUM HDFSDIRECTORY [ table_name ];

VALUES

根据给定的值表达式计算一个或一组行的值。它通常用于在一个较大的命令内生成一个“常数表”。

  1. VALUES {( 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 ];