All Products
Search
Document Center

AnalyticDB for PostgreSQL:SQL syntax

Last Updated:Nov 30, 2023

This topic describes the SQL statements that are available in AnalyticDB for PostgreSQL and the syntax of the statements.

Note

Some statements are not supported for AnalyticDB for PostgreSQL in Serverless mode. For more information, see Serverless mode.

ABORT

Terminates the current transaction.

ABORT [WORK | TRANSACTION]

For more information, see ABORT.

ALTER AGGREGATE

Changes the definition of an aggregate function.

ALTER AGGREGATE name ( type [ , ... ] ) RENAME TO new_name
ALTER AGGREGATE name ( type [ , ... ] ) OWNER TO new_owner
ALTER AGGREGATE name ( type [ , ... ] ) SET SCHEMA new_schema

For more information, see ALTER AGGREGATE.

ALTER CONVERSION

Changes the definition of a conversion.

ALTER CONVERSION name RENAME TO newname
ALTER CONVERSION name OWNER TO newowner

For more information, see ALTER CONVERSION.

ALTER DATABASE

Changes the attributes of a database.

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

For more information, see ALTER DATABASE.

ALTER DOMAIN

Changes the definition of a domain.

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

For more information, see ALTER ROLE.

ALTER EXTERNAL TABLE

Changes the definition of an external table.

ALTER EXTERNAL TABLE name RENAME [COLUMN] column TO new_column
ALTER EXTERNAL TABLE name RENAME TO new_name
ALTER EXTERNAL TABLE name SET SCHEMA new_schema
ALTER EXTERNAL TABLE name action [, ... ]

For more information, see ALTER EXTERNAL TABLE.

ALTER FUNCTION

Changes the definition of a 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
ALTER FUNCTION name ( [ [argmode] [argname] argtype [, ...] ] ) 
   SET SCHEMA new_schema

For more information, see ALTER FUNCTION.

ALTER GROUP

Changes a role name or membership.

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

For more information, see ALTER GROUP.

ALTER INDEX

Changes the definition of an index.

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

For more information, see ALTER INDEX.

ALTER OPERATOR

Changes the definition of an operator.

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

For more information, see ALTER OPERATOR.

ALTER RESOURCE QUEUE

Changes the limits of a resource queue.

ALTER RESOURCE QUEUE name WITH ( queue_attribute=value [, ... ] )

For more information, see ALTER RESOURCE QUEUE.

ALTER ROLE

Changes a database role (user or group).

ALTER ROLE name [ [ WITH ] option [ ... ] ]

WHERE option can be:
 SUPERUSER | NOSUPERUSER
 | CREATEDB | NOCREATEDB
 | CREATEROLE | NOCREATEROLE
 | CREATEEXTTABLE | NOCREATEEXTTABLE [ ( attribute='value' [, ...] )
 WHERE attributes and values are:
 type='readable'|'writable'
 protocol='gpfdist'|'http'
 | INHERIT | NOINHERIT
 | LOGIN | NOLOGIN
 | REPLICATION | NOREPLICATION
 | CONNECTION LIMIT connlimit
 | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
 | VALID UNTIL 'timestamp'
ALTER ROLE name RENAME TO new_name
ALTER ROLE { name | ALL } [ IN DATABASE database_name ]
 SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE { name | ALL } [ IN DATABASE database_name ]
 SET configuration_parameter FROM CURRENT
ALTER ROLE { name | ALL } [ IN DATABASE database_name ]
 RESET configuration_parameter
ALTER ROLE { name | ALL } [ IN DATABASE database_name ] RESET ALL
ALTER ROLE name RESOURCE QUEUE {queue_name | NONE}
ALTER ROLE name RESOURCE GROUP {group_name | NONE}

For more information, see ALTER ROLE.

ALTER SCHEMA

Changes the definition of a schema.

ALTER SCHEMA name RENAME TO newname

ALTER SCHEMA name OWNER TO newowner

For more information, see ALTER SCHEMA.

ALTER SEQUENCE

Changes the definition of a sequence generator.

ALTER SEQUENCE name [INCREMENT [ BY ] increment] 
     [MINVALUE minvalue | NO MINVALUE] 
     [MAXVALUE maxvalue | NO MAXVALUE] 
     [RESTART [ WITH ] start] 
     [CACHE cache] [[ NO ] CYCLE] 
     [OWNED BY {table.column | NONE}]
ALTER SEQUENCE name SET SCHEMA new_schema

For more information, see ALTER SEQUENCE.

ALTER TABLE

Changes the definition of a table.

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

ALTER TABLE name RENAME TO new_name

ALTER TABLE name SET SCHEMA new_schema

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

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

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

For more information, see ALTER TABLE.

ALTER TYPE

Changes the definition of a data type.

ALTER TYPE name
   OWNER TO new_owner | SET SCHEMA new_schema

For more information, see ALTER TYPE.

ALTER USER

Changes the definition of a database role (user).

ALTER USER name RENAME TO newname

ALTER USER name SET config_parameter {TO | =} {value | DEFAULT}

ALTER USER name RESET config_parameter

ALTER USER name [ [WITH] option [ ... ] ]

For more information, see ALTER USER.

ANALYZE

Collects statistics about a database.

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

For more information, see ANALYZE.

BEGIN

Starts a transaction block.

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

For more information, see BEGIN.

CHECKPOINT

Forces a transaction log checkpoint.

CHECKPOINT

For more information, see CHECKPOINT.

CLOSE

Disables a cursor.

CLOSE cursor_name

For more information, see CLOSE.

CLUSTER

Physically reorders heap storage tables on a disk based on an index. We recommend that you do not use this statement.

CLUSTER indexname ON tablename

CLUSTER tablename

CLUSTER

For more information, see CLUSTER.

COMMENT

Defines or changes the comments of an object.

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

For more information, see COMMENT.

COMMIT

Commits the current transaction.

COMMIT [WORK | TRANSACTION]

For more information, see COMMIT.

COPY

Copies data between a file and a table.

COPY table [(column [, ...])] FROM {'file' | STDIN}
     [ [WITH] 
       [BINARY]
       [OIDS]
       [HEADER]
       [DELIMITER [ AS ] 'delimiter']
       [NULL [ AS ] 'null string']
       [ESCAPE [ AS ] 'escape' | 'OFF']
       [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
       [CSV [QUOTE [ AS ] 'quote'] 
            [FORCE NOT NULL column [, ...]]
       [FILL MISSING FIELDS]
       [[LOG ERRORS]  
       SEGMENT REJECT LIMIT count [ROWS | PERCENT] ]

COPY {table [(column [, ...])] | (query)} TO {'file' | STDOUT}
      [ [WITH] 
        [ON SEGMENT]
        [BINARY]
        [OIDS]
        [HEADER]
        [DELIMITER [ AS ] 'delimiter']
        [NULL [ AS ] 'null string']
        [ESCAPE [ AS ] 'escape' | 'OFF']
        [CSV [QUOTE [ AS ] 'quote'] 
             [FORCE QUOTE column [, ...]] ]
      [IGNORE EXTERNAL PARTITIONS ]

For more information, see COPY.

CREATE AGGREGATE

Creates an aggregate function.

CREATE [ORDERED] AGGREGATE name (input_data_type [ , ... ]) 
      ( SFUNC = sfunc,
        STYPE = state_data_type
        [, PREFUNC = prefunc]
        [, FINALFUNC = ffunc]
        [, INITCOND = initial_condition]
        [, SORTOP = sort_operator] )

For more information, see CREATE AGGREGATE.

CREATE CAST

Creates a cast.

CREATE CAST (sourcetype AS targettype) 
       WITH FUNCTION funcname (argtypes) 
       [AS ASSIGNMENT | AS IMPLICIT]

CREATE CAST (sourcetype AS targettype) WITHOUT FUNCTION 
       [AS ASSIGNMENT | AS IMPLICIT]

For more information, see CREATE CAST.

CREATE CONVERSION

Creates an encoding conversion.

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

For more information, see CREATE CONVERSION.

CREATE DATABASE

Creates a database.

CREATE DATABASE name [ [WITH] [OWNER [=] dbowner]
                     [TEMPLATE [=] template]
                     [ENCODING [=] encoding]
                     [CONNECTION LIMIT [=] connlimit ] ]

For more information, see CREATE DATABASE.

CREATE DOMAIN

Creates a domain.

CREATE DOMAIN name [AS] data_type [DEFAULT expression] 
       [CONSTRAINT constraint_name
       | NOT NULL | NULL 
       | CHECK (expression) [...]]

For more information, see CREATE DOMAIN.

CREATE EXTENSION

Creates an extension in a database.

CREATE EXTENSION [ IF NOT EXISTS ] extension_name
  [ WITH ] [ SCHEMA schema_name ]
           [ VERSION version ]
           [ FROM old_version ]
           [ CASCADE ]

For more information, see CREATE EXTENSION.

CREATE EXTERNAL TABLE

Creates an external table.

CREATE [READABLE] EXTERNAL TABLE tablename
( columnname datatype [, ...] | LIKE othertable )
LOCATION ('ossprotocol')
FORMAT 'TEXT'
            [( [HEADER]
               [DELIMITER [AS] 'delimiter' | 'OFF']
               [NULL [AS] 'null string']
               [ESCAPE [AS] 'escape' | 'OFF']
               [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
               [FILL MISSING FIELDS] )]
           | 'CSV'
            [( [HEADER]
               [QUOTE [AS] 'quote']
               [DELIMITER [AS] 'delimiter']
               [NULL [AS] 'null string']
               [FORCE NOT NULL column [, ...]]
               [ESCAPE [AS] 'escape']
               [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
               [FILL MISSING FIELDS] )]
[ ENCODING 'encoding' ]
[ [LOG ERRORS [INTO error_table]] SEGMENT REJECT LIMIT count
       [ROWS | PERCENT] ]
CREATE WRITABLE EXTERNAL TABLE table_name
( column_name data_type [, ...] | LIKE other_table )
LOCATION ('ossprotocol')
FORMAT 'TEXT'
               [( [DELIMITER [AS] 'delimiter']
               [NULL [AS] 'null string']
               [ESCAPE [AS] 'escape' | 'OFF'] )]
          | 'CSV'
               [([QUOTE [AS] 'quote']
               [DELIMITER [AS] 'delimiter']
               [NULL [AS] 'null string']
               [FORCE QUOTE column [, ...]] ]
               [ESCAPE [AS] 'escape'] )]
[ ENCODING 'encoding' ]
[ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
ossprotocol:
   oss://oss_endpoint prefix=prefix_name
    id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]
ossprotocol:
   oss://oss_endpoint dir=[folder/[folder/]...]/file_name
    id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]
ossprotocol:
   oss://oss_endpoint filepath=[folder/[folder/]...]/file_name
id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]

For more information, see CREATE EXTERNAL TABLE.

CREATE FUNCTION

Creates a function.

CREATE [OR REPLACE] FUNCTION name    
    ( [ [argmode] [argname] argtype [ { DEFAULT | = } defexpr ] [, ...] ] )
      [ RETURNS { [ SETOF ] rettype 
        | TABLE ([{ argname argtype | LIKE other table }
          [, ...]])
        } ]
    { LANGUAGE langname
    | IMMUTABLE | STABLE | VOLATILE
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINE
    | COST execution_cost
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol' } ...
    [ WITH ({ DESCRIBE = describe_function
           } [, ...] ) ]

For more information, see CREATE FUNCTION.

CREATE GROUP

Creates a database role.

CREATE GROUP name [ [WITH] option [ ... ] ]

For more information, see CREATE GROUP.

CREATE INDEX

Creates an index.

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

AnalyticDB for PostgreSQL in Serverless mode allows you to create only B-tree indexes.

For more information, see CREATE INDEX.

CREATE LIBRARY

Creates a custom library package.

CREATE LIBRARY library_name LANGUAGE [JAVA] FROM oss_location OWNER ownername
CREATE LIBRARY library_name LANGUAGE [JAVA] VALUES file_content_hex OWNER ownername

For more information, see CREATE LIBRARY.

CREATE OPERATOR

Creates an operator.

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

For more information, see CREATE OPERATOR.

CREATE RESOURCE QUEUE

Creates a resource queue.

CREATE RESOURCE QUEUE name WITH (queue_attribute=value [, ... ])

For more information, see CREATE RESOURCE QUEUE.

CREATE ROLE

Creates a database role (user or group).

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

WHERE option can be:
SUPERUSER | NOSUPERUSER
 | CREATEDB | NOCREATEDB
 | CREATEROLE | NOCREATEROLE
 | CREATEUSER | NOCREATEUSER
 | CREATEEXTTABLE | NOCREATEEXTTABLE
 [ ( attribute='value'[, ...] ) ]
 WHERE attributes and value are:
 type='readable'|'writable'
 protocol='gpfdist'|'http'
 | INHERIT | NOINHERIT
 | LOGIN | NOLOGIN
 | REPLICATION | NOREPLICATION
 | CONNECTION LIMIT connlimit
 | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
 | VALID UNTIL 'timestamp'
 | IN ROLE rolename [, ...]
 | ROLE rolename [, ...]
 | ADMIN rolename [, ...]
 | USER rolename [, ...]
 | SYSID uid [, ...]
 | RESOURCE QUEUE queue_name
 | RESOURCE GROUP group_name
 | [ DENY deny_point ]
 | [ DENY BETWEEN deny_point AND deny_point]

The following table describes the attributes that are available for database roles.

Attribute

Description

CREATEDB | NOCREATEDB

Specifies whether the role is allowed to create databases. Default value: NOCREATEDB.

CREATEROLE | NOCREATEROLE

Specifies whether the role is allowed to create users. Default value: NOCREATEROLE.

INHERIT | NOINHERIT

Specifies whether the role inherits permissions from the group to which the role belongs. Default value: INHERIT.

LOGIN | NOLOGIN

Specifies whether the role has the logon permission. A role that has the logon permission is considered a user. A role that does not have the logon permission is considered a group. A user can inherit permissions from the group to which it belongs. Default value: NOLOGIN.

CONNECTION LIMIT connlimit

The maximum number of concurrent connections that the role can establish. The default value is -1, which indicates that no limitation.

CREATEEXTTABLE | NOCREATEEXTTABLE

Specifies whether the role is allowed to create external tables. Default value: NOCREATEEXTTABLE.

PASSWORD 'password'

The logon password of the role.

ENCRYPTED | UNENCRYPTED

Specifies whether the logon password is encrypted.

VALID UNTIL 'timestamp'

The expiration time of the logon password.

RESOURCE QUEUE queue_name

The name of the resource queue to which the role belongs.

DENY {deny_interval | deny_point}

Denies logon of the role at a time or within an interval.

For more information, see CREATE ROLE.

CREATE RULE

Creates a rewrite rule.

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

For more information, see CREATE RULE.

CREATE SCHEMA

Creates a schema.

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

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

For more information, see CREATE SCHEMA.

CREATE SEQUENCE

Creates a sequence generator.

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

For more information, see CREATE SEQUENCE.

CREATE TABLE

Creates a table.

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

AnalyticDB for PostgreSQL instances in Serverless mode do not support the WITH clause. The system selects the optimal algorithm based on the data type.

For more information, see CREATE TABLE.

CREATE TABLE AS

Creates a table from the results of a query.

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

For more information, see CREATE TABLE AS.

CREATE TRIGGER

Creates a trigger.

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

For more information, see CREATE TRIGGER.

CREATE TYPE

Creates a data type.

CREATE TYPE name AS ( attribute_name data_type [, ... ] )

CREATE TYPE name AS ENUM ( 'label' [, ... ] )

CREATE TYPE name (
    INPUT = input_function,
    OUTPUT = output_function
    [, RECEIVE = receive_function]
    [, SEND = send_function]
    [, TYPMOD_IN = type_modifier_input_function ]
    [, TYPMOD_OUT = type_modifier_output_function ]
    [, INTERNALLENGTH = {internallength | VARIABLE}]
    [, PASSEDBYVALUE]
    [, ALIGNMENT = alignment]
    [, STORAGE = storage]
    [, DEFAULT = default]
    [, ELEMENT = element]
    [, DELIMITER = delimiter] )

CREATE TYPE name

For more information, see CREATE TYPE.

CREATE USER

Creates a database role that has the LOGIN permission.

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

For more information, see CREATE USER.

CREATE VIEW

Creates a view.

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

For more information, see CREATE VIEW.

DEALLOCATE

Deallocates a prepared statement.

DEALLOCATE [PREPARE] name

For more information, see DEALLOCATE.

DECLARE

Creates a cursor.

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

For more information, see DECLARE.

DELETE

Deletes rows from a table.

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

For more information, see DELETE.

DROP AGGREGATE

Deletes an aggregate function.

DROP AGGREGATE [IF EXISTS] name ( type [, ...] ) [CASCADE | RESTRICT]

For more information, see DROP AGGREGATE.

DROP CAST

Deletes a cast.

DROP CAST [IF EXISTS] (sourcetype AS targettype) [CASCADE | RESTRICT]

For more information, see DROP CAST.

DROP CONVERSION

Deletes a conversion.

DROP CONVERSION [IF EXISTS] name [CASCADE | RESTRICT]

For more information, see DROP CONVERSION.

DROP DATABASE

Deletes a database.

DROP DATABASE [IF EXISTS] name

For more information, see DROP DATABASE.

DROP DOMAIN

Deletes a domain.

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

For more information, see DROP DOMAIN.

DROP EXTENSION

Deletes an extension from a database.

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

For more information, see DROP EXTENSION.

DROP EXTERNAL TABLE

Deletes an external table.

DROP EXTERNAL [WEB] TABLE [IF EXISTS] name [CASCADE | RESTRICT]

For more information, see DROP EXTERNAL TABLE.

DROP FUNCTION

Deletes a function.

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

For more information, see DROP FUNCTION.

DROP GROUP

Deletes a database role.

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

For more information, see DROP GROUP.

DROP INDEX

Deletes an index.

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

For more information, see DROP INDEX.

DROP LIBRARY

Deletes a custom library package.

DROP LIBRARY library_name

For more information, see DROP LIBRARY.

DROP OPERATOR

Deletes an operator.

DROP OPERATOR [IF EXISTS] name ( {lefttype | NONE} , 
    {righttype | NONE} ) [CASCADE | RESTRICT]

For more information, see DROP OPERATOR.

DROP OWNED

Deletes database objects that are owned by a database role.

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

For more information, see DROP OWNED.

DROP RESOURCE QUEUE

Deletes a resource queue.

DROP RESOURCE QUEUE queue_name

For more information, see DROP RESOURCE QUEUE.

DROP ROLE

Deletes a database role.

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

For more information, see DROP ROLE.

DROP RULE

Deletes a rewrite rule.

DROP RULE [IF EXISTS] name ON relation [CASCADE | RESTRICT]

For more information, see DROP RULE.

DROP SCHEMA

Deletes a schema.

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

For more information, see DROP SCHEMA.

DROP SEQUENCE

Deletes a sequence.

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

For more information, see DROP SEQUENCE.

DROP TABLE

Deletes a table.

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

For more information, see DROP TABLE.

DROP TYPE

Deletes a data type.

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

For more information, see DROP TYPE.

DROP USER

Deletes a database role.

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

For more information, see DROP USER.

DROP VIEW

Deletes a view.

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

For more information, see DROP VIEW.

END

Commits the current transaction.

END [WORK | TRANSACTION]

For more information, see END.

EXECUTE

Executes a prepared SQL statement.

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

For more information, see EXECUTE.

EXPLAIN

Shows the query plan of a statement.

EXPLAIN [ANALYZE] [VERBOSE] statement

For more information, see EXPLAIN.

FETCH

Retrieves rows from a query by using a cursor.

FETCH [ forward_direction { FROM | IN } ] cursorname

For more information, see FETCH.

GRANT

Grants access permissions.

GRANT { {SELECT | INSERT | UPDATE | DELETE | REFERENCES | 
TRIGGER | TRUNCATE } [,...] | ALL [PRIVILEGES] }
    ON [TABLE] tablename [, ...]
    TO {rolename | PUBLIC} [, ...] [WITH GRANT OPTION]

GRANT { {USAGE | SELECT | UPDATE} [,...] | ALL [PRIVILEGES] }
    ON SEQUENCE sequencename [, ...]
    TO { rolename | PUBLIC } [, ...] [WITH GRANT OPTION]

GRANT { {CREATE | CONNECT | TEMPORARY | TEMP} [,...] | ALL 
[PRIVILEGES] }
    ON DATABASE dbname [, ...]
    TO {rolename | PUBLIC} [, ...] [WITH GRANT OPTION]

GRANT { EXECUTE | ALL [PRIVILEGES] }
    ON FUNCTION funcname ( [ [argmode] [argname] argtype [, ...] 
] ) [, ...]
    TO {rolename | PUBLIC} [, ...] [WITH GRANT OPTION]

GRANT { USAGE | ALL [PRIVILEGES] }
    ON LANGUAGE langname [, ...]
    TO {rolename | PUBLIC} [, ...] [WITH GRANT OPTION]

GRANT { {CREATE | USAGE} [,...] | ALL [PRIVILEGES] }
    ON SCHEMA schemaname [, ...]
    TO {rolename | PUBLIC} [, ...] [WITH GRANT OPTION]

GRANT { CREATE | ALL [PRIVILEGES] }
    ON TABLESPACE tablespacename [, ...]
    TO {rolename | PUBLIC} [, ...] [WITH GRANT OPTION]

GRANT parent_role [, ...] 
    TO member_role [, ...] [WITH ADMIN OPTION]

GRANT { SELECT | INSERT | ALL [PRIVILEGES] } 
    ON PROTOCOL protocolname
    TO username

For more information, see GRANT.

INSERT

Inserts rows into a table.

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

For more information, see INSERT.

LOAD

Loads or reloads a shared library file.

LOAD 'filename'

For more information, see LOAD.

LOCK

Locks a table.

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

For more information, see LOCK.

MOVE

Positions a cursor.

MOVE [ forward_direction {FROM | IN} ] cursorname

For more information, see MOVE.

PREPARE

Prepares a statement for execution.

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

For more information, see PREPARE.

REASSIGN OWNED

Changes the ownership of database objects that are owned by a database role.

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

For more information, see REASSIGN OWNED.

REINDEX

Rebuilds an index.

REINDEX {INDEX | TABLE | DATABASE | SYSTEM} name

For more information, see REINDEX.

RELEASE SAVEPOINT

Releases a defined savepoint.

RELEASE [SAVEPOINT] savepoint_name

For more information, see RELEASE SAVEPOINT.

RESET

Restores the value of a system configuration parameter to the default value.

RESET configuration_parameter

RESET ALL

For more information, see RESET.

REVOKE

Revokes access permissions.

REVOKE [GRANT OPTION FOR] { {SELECT | INSERT | UPDATE | DELETE 
       | REFERENCES | TRIGGER | TRUNCATE } [,...] | ALL [PRIVILEGES] }
       ON [TABLE] tablename [, ...]
       FROM {rolename | PUBLIC} [, ...]
       [CASCADE | RESTRICT]

REVOKE [GRANT OPTION FOR] { {USAGE | SELECT | UPDATE} [,...] 
       | ALL [PRIVILEGES] }
       ON SEQUENCE sequencename [, ...]
       FROM { rolename | PUBLIC } [, ...]
       [CASCADE | RESTRICT]

REVOKE [GRANT OPTION FOR] { {CREATE | CONNECT 
       | TEMPORARY | TEMP} [,...] | ALL [PRIVILEGES] }
       ON DATABASE dbname [, ...]
       FROM {rolename | PUBLIC} [, ...]
       [CASCADE | RESTRICT]

REVOKE [GRANT OPTION FOR] {EXECUTE | ALL [PRIVILEGES]}
       ON FUNCTION funcname ( [[argmode] [argname] argtype
                              [, ...]] ) [, ...]
       FROM {rolename | PUBLIC} [, ...]
       [CASCADE | RESTRICT]

REVOKE [GRANT OPTION FOR] {USAGE | ALL [PRIVILEGES]}
       ON LANGUAGE langname [, ...]
       FROM {rolename | PUBLIC} [, ...]
       [ CASCADE | RESTRICT ]

REVOKE [GRANT OPTION FOR] { {CREATE | USAGE} [,...] 
       | ALL [PRIVILEGES] }
       ON SCHEMA schemaname [, ...]
       FROM {rolename | PUBLIC} [, ...]
       [CASCADE | RESTRICT]

REVOKE [GRANT OPTION FOR] { CREATE | ALL [PRIVILEGES] }
       ON TABLESPACE tablespacename [, ...]
       FROM { rolename | PUBLIC } [, ...]
       [CASCADE | RESTRICT]

REVOKE [ADMIN OPTION FOR] parent_role [, ...] 
       FROM member_role [, ...]
       [CASCADE | RESTRICT]

For more information, see REVOKE.

ROLLBACK

Stops the current transaction.

ROLLBACK [WORK | TRANSACTION]

For more information, see ROLLBACK.

ROLLBACK TO SAVEPOINT

Rolls back the current transaction to a savepoint.

ROLLBACK [WORK | TRANSACTION] TO [SAVEPOINT] savepoint_name

For more information, see ROLLBACK TO SAVEPOINT.

SAVEPOINT

Creates a savepoint in the current transaction.

SAVEPOINT savepoint_name

For more information, see SAVEPOINT.

SELECT

Retrieves rows from a table or a view.

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

For more information, see SELECT.

SELECT INTO

Creates a table from the results of a query.

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

For more information, see SELECT INTO.

SET

Changes the value of a database configuration parameter.

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

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

For more information, see SET.

SET ROLE

Sets the current role identifier of the current session.

SET [SESSION | LOCAL] ROLE rolename

SET [SESSION | LOCAL] ROLE NONE

RESET ROLE

For more information, see SET ROLE.

SET SESSION AUTHORIZATION

Sets the session role identifier and the current role identifier of the current session.

SET [SESSION | LOCAL] SESSION AUTHORIZATION rolename

SET [SESSION | LOCAL] SESSION AUTHORIZATION DEFAULT

RESET SESSION AUTHORIZATION

For more information, see SET SESSION AUTHORIZATION.

SET TRANSACTION

Sets the characteristics of the current transaction.

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

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

For more information, see SET TRANSACTION.

SHOW

Shows the value of a system configuration parameter.

SHOW configuration_parameter

SHOW ALL

For more information, see SHOW.

START TRANSACTION

Starts a transaction block.

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

For more information, see START TRANSACTION.

TRUNCATE

Clears all rows of a table.

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

For more information, see TRUNCATE.

UPDATE

Updates rows of a table.

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

For more information, see UPDATE.

VACUUM

Reclaims space from a database and optionally analyzes the database.

VACUUM [FULL] [FREEZE] [VERBOSE] [table]

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

For more information, see VACUUM.

VALUES

Computes a set of row values.

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

For more information, see VALUES.