Syntax supported by ADB for PostgreSQL

Contents

• CREATE INDEX

• CREATE ROLE

• CREATE SEQUENCE

• CREATE USER

• CREATE VIEW

• CREATE SCHEMA

• CREATE TABLE

• CREATE TABLE AS

• SELECT

• ALTER DATABASE

• ALTER INDEX

• ALTER SCHEMA

• ALTER TABLE

• DROP INDEX

• DROP SCHEMA

• DROP ROLE

• DROP SEQUENCE

• DROP USER

• DROP VIEW

• DROP TABLE

• BEGIN

• CHECKPOINT

• CLOSE

• CLUSTER

• COMMENT

• COMMIT

• DEALLOCATE

• DECLARE

• DELETE

• END

• EXPLAIN

• FETCH

• LOAD

• LOCK

• MOVE

• PREPARE

• REASSIGN OWNED

• REINDEX

• RELEASE SAVEPOINT

• ROLLBACK

• ROLLBACK TO SAVEPOINT

• SAVEPOINT

• SELECT INTO

• SET

• SET ROLE

• SET SESSION AUTHORIZATION

• SET TRANSACTION

• SHOW

• START TRANSACTION

• TRUNCATE

• UPDATE

• VACUUM

• VALUES

CREATE INDEX

Creates an index on a table.

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

For more information, see CREATE INDEX.

CREATE ROLE

Creates a database role. A role can represent a user or a 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]

For more information, see CREATE ROLE.

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 USER

Creates a database role with the LOGIN privilege by default.

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.

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 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
           [(...)] 
         ) ] 
    )

For more information, see CREATE TABLE.

CREATE TABLE AS

Creates a new 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.

SELECT

Retrieves rows from a table or view.

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

For more information, see SELECT.

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 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 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 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.

DROP INDEX

Drops an index.

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

For more information, see DROP INDEX.

DROP SCHEMA

Drops a schema.

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

For more information, see DROP SCHEMA.

DROP ROLE

Drops a database role.

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

For more information, see DROP ROLE.

DROP SEQUENCE

Drops a sequence.

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

For more information, see DROP SEQUENCE.

DROP USER

Drops a database user.

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

For more information, see DROP USER.

DROP VIEW

Drops a view.

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

For more information, see DROP VIEW.

DROP TABLE

Drops a table.

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

For more information, see DROP TABLE.

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

Closes a cursor.

CLOSE cursor_name

For more information, see CLOSE.

CLUSTER

Physically reorders a heap storage table on disk based on an index. Not recommended for use in production.

CLUSTER indexname ON tablename

CLUSTER tablename

CLUSTER

For more information, see CLUSTER.

COMMENT

Adds or changes the comment on a database 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.

DEALLOCATE

Deallocates a prepared statement.

DEALLOCATE [PREPARE] name

For more information, see DEALLOCATE.

DECLARE

Defines a cursor for iterating over a query result.

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.

END

Commits the current transaction. Equivalent to COMMIT.

END [WORK | TRANSACTION]

For more information, see END.

EXPLAIN

Shows the execution plan for a statement.

EXPLAIN [ANALYZE] [VERBOSE] statement

For more information, see EXPLAIN.

FETCH

Retrieves rows from a query result using a cursor.

FETCH [ forward_direction { FROM | IN } ] cursorname

For more information, see FETCH.

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

Advances a cursor position without retrieving rows.

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

Transfers ownership of all objects belonging to a database role to another role.

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

For more information, see REASSIGN OWNED.

REINDEX

Rebuilds one or more indexes.

REINDEX {INDEX | TABLE | DATABASE | SYSTEM} name

For more information, see REINDEX.

RELEASE SAVEPOINT

Releases a previously defined savepoint.

RELEASE [SAVEPOINT] savepoint_name

For more information, see RELEASE SAVEPOINT.

ROLLBACK

Aborts the current transaction.

ROLLBACK [WORK | TRANSACTION]

For more information, see ROLLBACK.

ROLLBACK TO SAVEPOINT

Rolls the current transaction back to a savepoint.

ROLLBACK [WORK | TRANSACTION] TO [SAVEPOINT] savepoint_name

For more information, see ROLLBACK TO SAVEPOINT.

SAVEPOINT

Defines a savepoint within the current transaction.

SAVEPOINT savepoint_name

For more information, see SAVEPOINT.

SELECT INTO

Creates a new 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

Sets a database configuration parameter for the current session.

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 for the 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 user and current role for 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 isolation level and access mode for 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

Displays the current value of a 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

Removes all rows from a table without scanning each row.

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

For more information, see TRUNCATE.

UPDATE

Updates rows in 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 storage and updates table statistics by performing garbage collection.

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

For more information, see VACUUM.

VALUES

Computes a set of rows from value expressions.

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

For more information, see VALUES.