Dataphin:ADB for PostgreSQL supported syntax

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

This command defines an index.

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

For more information, see the referenced document.

CREATE ROLE

This command defines a new database role, which can be either a user or a group.

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

For more information, see the referenced document.

CREATE SEQUENCE

This command establishes a new 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 the referenced document.

CREATE USER

Creates a new database role with default LOGIN permissions.

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

For more information, see the referenced document.

CREATE VIEW

This command defines a view.

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

For more information, see the referenced document.

CREATE SCHEMA

This command defines a new schema.

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

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

For more information, see the referenced document.

CREATE TABLE

This command defines a new table within the database.

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 the referenced document.

CREATE TABLE AS

This command defines a new table based on the results of a specified 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 the referenced document.

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 the referenced document.

ALTER DATABASE

This command modifies properties 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 the referenced document.

ALTER INDEX

Modifies an index's configuration.

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 the referenced document.

ALTER SCHEMA

Modifies a schema's definition.

ALTER SCHEMA name RENAME TO newname

ALTER SCHEMA name OWNER TO newowner

For more information, see the referenced document.

ALTER TABLE

Modifies a table's structure.

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 the referenced document.

DROP INDEX

Removes a specified index.

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

For more information, see the referenced document.

DROP SCHEMA

This command removes a schema.

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

For more information, see the referenced document.

DROP ROLE

This command removes a database role.

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

For more information, see the referenced document.

DROP SEQUENCE

This command removes a specified sequence.

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

For more information, see the referenced document.

DROP USER

Removes a database role.

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

For more information, see the referenced document.

DROP VIEW

This command removes a specified view from the database.

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

For additional details, see the referenced document.

DROP TABLE

This command removes a table from the database.

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

For more information, see the referenced document.

BEGIN

Initiates a transaction block.

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

For more information, see the referenced document.

CHECKPOINT

Initiates a transaction log checkpoint.

CHECKPOINT

For more information, see the referenced document.

CLOSE

This command closes an open cursor.

CLOSE cursor_name

For more information, see the referenced document.

CLUSTER

Reorganizes a heap storage table on disk to align with a specified index.

CLUSTER indexname ON tablename

CLUSTER tablename

CLUSTER

For more information, see the referenced document.

COMMENT

Allows the definition or modification of a comment associated with an object.

COMMENT ON
{ TABLE object_name |
  COLUMN table_name.column_name |
  AGGREGATE agg_name (agg_type [, ...]) |
  CAST (source_type AS target_type) |
  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 (left_operand_type, right_operand_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 the referenced document.

COMMIT

This command finalizes the current transaction.

COMMIT [WORK | TRANSACTION]

For more information, see the referenced document.

DEALLOCATE

This command deallocates a precompiled statement.

DEALLOCATE [PREPARE] name

For more information, see the referenced document.

DECLARE

This command is used to define a cursor.

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

For more information, see the referenced document.

DELETE

This command removes rows from a specified table.

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

For more information, see the referenced document.

END

This command finalizes the current transaction.

END [WORK | TRANSACTION]

For more information, see the referenced document.

EXPLAIN

This command displays the execution plan for a specific SQL statement.

EXPLAIN [ANALYZE] [VERBOSE] statement

For additional details, see the referenced document.

FETCH

Retrieves rows from a query result set through a cursor.

FETCH [ forward_direction { FROM | IN } ] cursorname

For more information, see the referenced document.

LOAD

This command loads or reloads a shared library file.

LOAD 'filename'

For additional details, see the referenced document.

LOCK

This command locks a specified table.

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

For more information, see the referenced document.

MOVE

This command positions a cursor.

MOVE [ forward_direction {FROM | IN} ] cursorname

For more information, see the referenced document.

PREPARE

This command prepares a statement for execution.

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

For more information, see the referenced document.

REASSIGN OWNED

This command reassigns objects owned by one database role to another.

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

For more information, see the referenced document.

REINDEX

Reconstructs an existing index.

REINDEX {INDEX | TABLE | DATABASE | SYSTEM} name

For more information, see the referenced document.

RELEASE SAVEPOINT

This command removes a previously established savepoint.

RELEASE [SAVEPOINT] savepoint_name

For more information, see the referenced document.

ROLLBACK

Terminates the current transaction.

ROLLBACK [WORK | TRANSACTION]

For more information, see the referenced document.

ROLLBACK TO SAVEPOINT

This command reverts the current transaction to a previously defined savepoint.

ROLLBACK [WORK | TRANSACTION] TO [SAVEPOINT] savepoint_name

For more information, see the referenced document.

SAVEPOINT

Creates a new savepoint within the ongoing transaction.

SAVEPOINT savepoint_name

For more information, see the referenced document.

SELECT INTO

Creates a new table based on 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 the referenced document.

SET

Modifies 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 the referenced document.

SET ROLE

This command assigns a specific role identifier for the current session.

SET [SESSION | LOCAL] ROLE rolename;

SET [SESSION | LOCAL] ROLE NONE;

RESET ROLE;

For more information, see the referenced document.

SET SESSION AUTHORIZATION

This command sets both the session user identifier and the current user identifier for the active session.

SET [SESSION | LOCAL] SESSION AUTHORIZATION rolename;

SET [SESSION | LOCAL] SESSION AUTHORIZATION DEFAULT;

RESET SESSION AUTHORIZATION;

For more information, see the referenced document.

SET TRANSACTION

This command configures the properties 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 the referenced document.

SHOW

This command displays the value of a specified system configuration parameter.

SHOW configuration_parameter

SHOW ALL

For more information, see the referenced document.

START TRANSACTION

Initiates a transaction block.

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

For more information, see the referenced document.

TRUNCATE

Removes all rows from a table without deleting the table itself.

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

For more information, see the referenced document.

UPDATE

This command updates rows within 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 the referenced document.

VACUUM

Initiates garbage collection.

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

For more information, see the referenced document.

VALUES

This command computes a collection of rows.

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

For more information, see the referenced document.