Creating a Table in Greenplum

Note: Greenplum temp tables (created with syntax create temporary table …), they cannot be pre-created and also cannot be assigned to a specific schema. They are created dynamically within a session and get dropped when the session terminates. Temporary tables are beneficial when multiple sessions need to work in parallel with their own versions (data, definition or both) of the same temp table. Greenplum will internally ensure that these are stored, accessed and dropped separately. So, every session/connection can have their own implementation of a temp table named T1. We can create permanent tables in specific schemas and use them like temp tables, but the data content will be shared by all sessions. Depending on the application’s need, you should use either of the implementations.

CREATE TABLE

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

CREATE TABLE AS

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]

ALTER TABLE

Changes the definition of a table.

Synopsis

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

where action is one of:

ADD [COLUMN] column_name type

[column_constraint [ ... ]]

DROP [COLUMN] column [RESTRICT | CASCADE]

ALTER [COLUMN] column TYPE type [USING expression]

ALTER [COLUMN] column SET DEFAULT expression

ALTER [COLUMN] column DROP DEFAULT

ALTER [COLUMN] column { SET | DROP } NOT NULL

ALTER [COLUMN] column SET STATISTICS integer

ADD table_constraint

DROP CONSTRAINT constraint_name [RESTRICT | CASCADE]

DISABLE TRIGGER [trigger_name | ALL | USER]

ENABLE TRIGGER [trigger_name | ALL | USER]

CLUSTER ON index_name

SET WITHOUT CLUSTER

SET WITHOUT OIDS

SET (FILLFACTOR = value)

RESET (FILLFACTOR)

INHERIT parent_table

NO INHERIT parent_table

OWNER TO new_owner

SET TABLESPACE new_tablespace

where partition_action is one of:

ALTER DEFAULT PARTITION

DROP DEFAULT PARTITION [IF EXISTS]

DROP PARTITION [IF EXISTS] { partition_name |

FOR (RANK(number)) | FOR (value) } [CASCADE]

TRUNCATE DEFAULT PARTITION

TRUNCATE PARTITION { partition_name | FOR (RANK(number)) |

FOR (value) }

RENAME DEFAULT PARTITION TO new_partition_name

RENAME PARTITION { partition_name | FOR (RANK(number)) |

FOR (value) } TO new_partition_name

ADD DEFAULT PARTITION name [ ( subpartition_spec ) ]

ADD PARTITION [name] partition_element

[ ( subpartition_spec ) ]

EXCHANGE PARTITION { partition_name | FOR (RANK(number)) |

FOR (value) } WITH TABLE table_name

[ WITH | WITHOUT VALIDATION ]

EXCHANGE DEFAULT PARTITION WITH TABLE table_name

[ WITH | WITHOUT VALIDATION ]

SET SUBPARTITION TEMPLATE (subpartition_spec)

SPLIT DEFAULT PARTITION

{ AT (list_value)

| START([datatype] range_value) [INCLUSIVE | EXCLUSIVE]

END([datatype] range_value) [INCLUSIVE | EXCLUSIVE] }

[ INTO ( PARTITION new_partition_name,

PARTITION default_partition_name ) ]

SPLIT PARTITION { partition_name | FOR (RANK(number)) |

FOR (value) } AT (value)

[ INTO (PARTITION partition_name, PARTITION

partition_name)]

where partition_element is:

VALUES (list_value [,...] )

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

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

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

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

[ TABLESPACE tablespace ]

where subpartition_spec is:

subpartition_element [, ...]

and subpartition_element is:

DEFAULT SUBPARTITION subpartition_name

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

| [SUBPARTITION subpartition_name]

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

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

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

| [SUBPARTITION subpartition_name]

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

[ EVERY ( [number | datatype] '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]