Physically reorders a heap storage table on disk according to an index. Not a recommended operation

in Greenplum Database.

CLUSTER indexname ON tablename

CLUSTER tablename

CLUSTER

Defines or change the comment 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'

Commits the current transaction.

COMMIT [WORK | TRANSACTION]

COPY

Copies data between a file and a table.

COPY table [(column [, ...])] FROM {'file' | STDIN}

[ [WITH]

[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 INTO error_table] [KEEP]

SEGMENT REJECT LIMIT count [ROWS | PERCENT] ]

COPY {table [(column [, ...])] | (query)} TO {'file' | STDOUT}

[ [WITH]

[OIDS]

[HEADER]

[DELIMITER [ AS ] 'delimiter']

[NULL [ AS ] 'null string']

[ESCAPE [ AS ] 'escape' | 'OFF']

[CSV [QUOTE [ AS ] 'quote']

[FORCE QUOTE column [, ...]] ]

Defines a new 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] )

Defines a new 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]

Defines a new encoding conversion.

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

Creates a new database.

CREATE DATABASE name [ [WITH] [OWNER [=] dbowner]

[TEMPLATE [=] template]

[ENCODING [=] encoding]

[TABLESPACE [=] tablespace]

[CONNECTION LIMIT [=] connlimit ] ]

Defines a new domain.

CREATE DOMAIN name [AS] data_type [DEFAULT expression]

[CONSTRAINT constraint_name

| NOT NULL | NULL

| CHECK (expression) [...]]

Defines a new external table.

CREATE [READABLE] EXTERNAL TABLE table_name

( column_name data_type [, ...] | LIKE other_table )

LOCATION ('file://seghost[:port]/path/file' [, ...])

| ('gpfdist://filehost[:port]/file_pattern[#transform]'

| ('gpfdists://filehost[:port]/file_pattern[#transform]'

[, ...])

| ('gphdfs://hdfs_host[:port]/path/file')

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] )]

| 'CUSTOM' (Formatter=<formatter specifications>)

[ ENCODING 'encoding' ]

[ [LOG ERRORS INTO error_table] SEGMENT REJECT LIMIT count

[ROWS | PERCENT] ]

CREATE [READABLE] EXTERNAL WEB TABLE table_name

( column_name data_type [, ...] | LIKE other_table )

LOCATION ('http://webhost[:port]/path/file' [, ...])

| EXECUTE 'command' [ON ALL

| MASTER

| number_of_segments

| HOST ['segment_hostname']

| SEGMENT segment_id ]

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] )]

| 'CUSTOM' (Formatter=<formatter specifications>)

[ 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('gpfdist://outputhost[:port]/filename[#transform]'

| ('gpfdists://outputhost[:port]/file_pattern[#transform]'

[, ...])

| ('gphdfs://hdfs_host[:port]/path')

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'] )]

| 'CUSTOM' (Formatter=<formatter specifications>)

[ ENCODING 'write_encoding' ]

[ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]

CREATE WRITABLE EXTERNAL WEB TABLE table_name

( column_name data_type [, ...] | LIKE other_table )

EXECUTE 'command' [ON ALL]

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'] )]

| 'CUSTOM' (Formatter=<formatter specifications>)

[ ENCODING 'write_encoding' ]

[ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]

Defines a new function.

CREATE [OR REPLACE] FUNCTION name

( [ [argmode] [argname] argtype [, ...] ] )

[ 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 DEFINER

| AS 'definition'

| AS 'obj_file', 'link_symbol' } ...

[ WITH ({ DESCRIBE = describe_function

} [, ...] ) ]

Defines a new database role.

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

where option can be:

SUPERUSER | NOSUPERUSER

| CREATEDB | NOCREATEDB

| CREATEROLE | NOCREATEROLE

| CREATEUSER | NOCREATEUSER

| INHERIT | NOINHERIT

| LOGIN | NOLOGIN

| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'

| VALID UNTIL 'timestamp'

| IN ROLE rolename [, ...]

| IN GROUP rolename [, ...]

| ROLE rolename [, ...]

| ADMIN rolename [, ...]

| USER rolename [, ...]

| SYSID uid

Defines a new index.

CREATE [UNIQUE] INDEX name ON table

[USING btree|bitmap|gist]

( {column | (expression)} [opclass] [, ...] )

[ WITH ( FILLFACTOR = value ) ]

[TABLESPACE tablespace]

[WHERE predicate]

Defines a new procedural language.

CREATE [PROCEDURAL] LANGUAGE name

CREATE [TRUSTED] [PROCEDURAL] LANGUAGE name

HANDLER call_handler [VALIDATOR valfunction]

Defines a new 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] )

Defines a new operator class.

CREATE OPERATOR CLASS name [DEFAULT] FOR TYPE data_type

USING index_method AS

{

OPERATOR strategy_number op_name [(op_type, op_type)] [RECHECK]

| FUNCTION support_number funcname (argument_type [, ...] )

| STORAGE storage_type

} [, ... ]

Defines a new resource queue.

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

where queue_attribute is:

ACTIVE_STATEMENTS=integer

[ MAX_COST=float [COST_OVERCOMMIT={TRUE|FALSE}] ]

[ MIN_COST=float ]

[ PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX} ]

[ MEMORY_LIMIT='memory_units' ]

| MAX_COST=float [ COST_OVERCOMMIT={TRUE|FALSE} ]

[ ACTIVE_STATEMENTS=integer ]

[ MIN_COST=float ]

[ PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX} ]

[ MEMORY_LIMIT='memory_units' ]

Defines a new database role (user or group).

CREATE 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

| CONNECTION LIMIT connlimit

| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'

| VALID UNTIL 'timestamp'

| IN ROLE rolename [, ...]

| ROLE rolename [, ...]

| ADMIN rolename [, ...]

| RESOURCE QUEUE queue_name

| [ DENY deny_point ]

| [ DENY BETWEEN deny_point AND deny_point]

Defines a new rewrite rule.

CREATE [OR REPLACE] RULE name AS ON event

TO table [WHERE condition]

DO [ALSO | INSTEAD] { NOTHING | command | (command; command ...) }

Defines a new schema.

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

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

Defines 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 }]

Defines a new 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}] ...}

[, ... ] ]

[column_reference_storage_directive [, …] ]

)

[ INHERITS ( parent_table [, ... ] ) ]

[ WITH ( storage_parameter=value [, ... ] )

[ ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP} ]

[ TABLESPACE tablespace ]

[ 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

[(...)]

) ]

)

where storage_parameter is:

APPENDONLY={TRUE|FALSE}

BLOCKSIZE={8192-2097152}

ORIENTATION={COLUMN|ROW}

COMPRESSTYPE={ZLIB|QUICKLZ|RLE_TYPE|NONE}

COMPRESSLEVEL={0-9}

FILLFACTOR={10-100}

OIDS[=TRUE|FALSE]

where column_constraint is:

[CONSTRAINT constraint_name]

NOT NULL | NULL

| UNIQUE [USING INDEX TABLESPACE tablespace]

[WITH ( FILLFACTOR = value )]

| PRIMARY KEY [USING INDEX TABLESPACE tablespace]

[WITH ( FILLFACTOR = value )]

| CHECK ( expression )

and table_constraint is:

[CONSTRAINT constraint_name]

UNIQUE ( column_name [, ... ] )

[USING INDEX TABLESPACE tablespace]

[WITH ( FILLFACTOR=value )]

| PRIMARY KEY ( column_name [, ... ] )

[USING INDEX TABLESPACE tablespace]

[WITH ( FILLFACTOR=value )]

| CHECK ( expression )

where partition_type is:

LIST

| RANGE

where partition_specification is:

partition_element [, ...]

and partition_element is:

DEFAULT PARTITION name

| [PARTITION name] VALUES (list_value [,...] )

| [PARTITION name]

START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE]

[ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ]

[ EVERY ([datatype] [number | INTERVAL] 'interval_value') ]

| [PARTITION name]

END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE]

[ EVERY ([datatype] [number | INTERVAL] 'interval_value') ]

[ WITH ( partition_storage_parameter=value [, ... ] ) ]

[column_reference_storage_directive [, …] ]

[ TABLESPACE tablespace ]

where subpartition_spec or template_spec is:

subpartition_element [, ...]

and subpartition_element is:

DEFAULT SUBPARTITION name

| [SUBPARTITION name] VALUES (list_value [,...] )

| [SUBPARTITION name]

START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE]

[ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ]

[ EVERY ([datatype] [number | INTERVAL] 'interval_value') ]

| [SUBPARTITION name]

END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE]

[ EVERY ([datatype] [number | INTERVAL] 'interval_value') ]

[ WITH ( partition_storage_parameter=value [, ... ] ) ]

[column_reference_storage_directive [, …] ]

[ TABLESPACE tablespace ]

where storage_parameter is:

APPENDONLY={TRUE|FALSE}

BLOCKSIZE={8192-2097152}

ORIENTATION={COLUMN|ROW}

COMPRESSTYPE={ZLIB|QUICKLZ|RLE_TYPE|NONE}

COMPRESSLEVEL={0-9}

FILLFACTOR={10-100}

OIDS[=TRUE|FALSE]

where storage_directive is:

COMPRESSTYPE={ZLIB | QUICKLZ | RLE_TYPE | NONE}

| COMPRESSLEVEL={0-9}

| BLOCKSIZE={8192-2097152}

Where column_reference_storage_directive is:

COLUMN column_name ENCODING (storage_directive [, ... ] ), ...

| 

DEFAULT COLUMN ENCODING (storage_directive [, ... ] )

Defines 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]

where storage_parameter is:

APPENDONLY={TRUE|FALSE}

BLOCKSIZE={8192-2097152}

ORIENTATION={COLUMN|ROW}

COMPRESSTYPE={ZLIB|QUICKLZ}

COMPRESSLEVEL={1-9 | 1}

FILLFACTOR={10-100}

OIDS[=TRUE|FALSE]

Defines a new tablespace.

CREATE TABLESPACE tablespace_name [OWNER username]

FILESPACE filespace_name

Defines a new trigger. User-defined triggers are not supported in Greenplum Database.

CREATE TRIGGER name {BEFORE | AFTER} {event [OR ...]}

ON table [ FOR [EACH] {ROW | STATEMENT} ]

EXECUTE PROCEDURE funcname ( arguments )

CREATE TYPE

Defines a new data type.

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

CREATE TYPE name (

INPUT = input_function,

OUTPUT = output_function

[, RECEIVE = receive_function]

[, SEND = send_function]

[, INTERNALLENGTH = {internallength | VARIABLE}]

[, PASSEDBYVALUE]

[, ALIGNMENT = alignment]

[, STORAGE = storage]

[, DEFAULT = default]

[, ELEMENT = element]

[, DELIMITER = delimiter]

[, COMPRESSTYPE = compression_type]

[, COMPRESSLEVEL = compression_level]

[, BLOCKSIZE= blocksize]

)

CREATE TYPE name

Defines a new database role with the LOGIN privilege by default.

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

where option can be:

SUPERUSER | NOSUPERUSER

| CREATEDB | NOCREATEDB

| CREATEROLE | NOCREATEROLE

| CREATEUSER | NOCREATEUSER

| INHERIT | NOINHERIT

| LOGIN | NOLOGIN

| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'

| VALID UNTIL 'timestamp'

| IN ROLE rolename [, ...]

| IN GROUP rolename [, ...]

| ROLE rolename [, ...]

| ADMIN rolename [, ...]

| USER rolename [, ...]

| SYSID uid

| RESOURCE QUEUE queue_name

Defines a new view.

CREATE [OR REPLACE] [TEMP | TEMPORARY] VIEW name

[ ( column_name [, ...] ) ]

AS query