GREENPLUM: CREATE TABLE Syntax

GREENPLUM: CREATE TABLE SYNTAX

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

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

| REFERENCES table_name [ ( column_name [, ... ] ) ]

[ key_match_type ]

[ key_action ]

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 )

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

REFERENCES table_name [ ( column_name [, ... ] ) ]

[ key_match_type ]

[ key_action ]

[ key_checking_mode ]

where key_match_type is:

MATCH FULL

| SIMPLE

where key_action is:

ON DELETE

| ON UPDATE

| NO ACTION

| RESTRICT

| CASCADE

| SET NULL

| SET DEFAULT

where key_checking_mode is:

DEFERRABLE

| NOT DEFERRABLE

| INITIALLY DEFERRED

| INITIALLY IMMEDIATE

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

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

[ TABLESPACE tablespace ]

where storage_parameter is:

APPENDONLY={TRUE|FALSE}

BLOCKSIZE={8192-2097152}

ORIENTATION={COLUMN|ROW}

COMPRESSTYPE={ZLIB|QUICKLZ|NONE}

COMPRESSLEVEL={0-9}

FILLFACTOR={10-100}

OIDS[=TRUE|FALSE]

{0-9}......