Creates a new table in Greenplum. You define a list of columns, each of which holds data of a distinct type. The owner of the table is the issuer of the CREATE TABLE command.
For more information, please refer to CREATE TABLE documentation.
Grammar Syntax
CREATE [ [GLOBAL | LOCAL] {TEMPORARY | TEMP} | UNLOGGED ] TABLE [IF NOT EXISTS] <table_name> (
[ { <column_name> <data_type> [ COLLATE <collation> ] [ ENCODING ( <storage_directive> [, ...] ) ] [<column_constraint> [ ... ] ]
| <table_constraint>
| LIKE <source_table> [ <like_option> ... ]
| COLUMN <column_name> ENCODING ( <storage_directive> [, ...] ) [, ...] }
] )
[ INHERITS ( <parent_table> [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { <column_name> | ( <expression> ) } [ COLLATE <collation> ] [ <opclass> ] [, ... ] ) ]
[ USING ( <access_method> ) ]
[ WITH ( <storage_parameter> [=<value>] [, ... ] ) ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE <tablespace_name> ]
[ DISTRIBUTED BY ( <column> [<opclass>] [, ... ] )
| DISTRIBUTED RANDOMLY
| DISTRIBUTED REPLICATED ]
CREATE [ [GLOBAL | LOCAL] {TEMPORARY | TEMP} | UNLOGGED ] TABLE [IF NOT EXISTS] <table_name>
OF <type_name> [ (
{ <column_name> [WITH OPTIONS] [ <column_constraint> [ ... ] ]
| <table_constraint> }
[, ... ]
) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { <column_name> | ( <expression> ) } [ COLLATE <collation> ] [ <opclass> ] [, ... ] ) ]
[ USING <access_method> ]
[ WITH ( <storage_parameter> [=<value>] [, ... ] ) ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE <tablespace_name> ]
[ DISTRIBUTED BY ( <column> [<opclass>] [, ... ] )
| DISTRIBUTED RANDOMLY
| DISTRIBUTED REPLICATED ]
CREATE [ [GLOBAL | LOCAL] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <table_name>
PARTITION OF <parent_table> [ (
{ <column_name [ WITH OPTIONS ] [ <column_constraint> [ ... ] ]
| <table_constraint> }
[, ... ]
) ] { FOR VALUES <partition_bound_spec> | DEFAULT }
[ PARTITION BY { RANGE | LIST | HASH } ( { <column_name> | ( <expression> ) } [ COLLATE <collation> ] [ <opclass> ] [, ... ] ) ]
[ USING <access_method> ]
[ WITH ( <storage_parameter> [=<value>] [, ... ] ) ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE <tablespace_name> ]
where <column_constraint> is:
[ CONSTRAINT <constraint_name>]
{ NOT NULL
| NULL
| CHECK ( <expression> ) [ NO INHERIT ]
| DEFAULT <default_expr>
| GENERATED ALWAYS AS ( <generation_expr> ) STORED
| GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <sequence_options> ) ]
| UNIQUE <index_parameters>
| PRIMARY KEY <index_parameters>
| REFERENCES <reftable> [ ( refcolumn ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <referential_action> ] [ ON UPDATE <referential_action> ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
and <table_constraint> is:
[ CONSTRAINT <constraint_name> ]
{ CHECK ( <expression> ) [ NO INHERIT ]
| UNIQUE ( <column_name> [, ... ] ) <index_parameters>
| PRIMARY KEY ( <column_name> [, ... ] ) <index_parameters>
| EXCLUDE [ USING <index_method> ] ( <exclude_element> WITH <operator> [, ... ] )
<index_parameters> [ WHERE ( <predicate> ) ]
| FOREIGN KEY ( <column_name> [, ... ] ) REFERENCES <reftable> [ ( <refcolumn> [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <referential_action> ] [ ON UPDATE <referential_action> ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
and <like_option> is:
{ INCLUDING | EXCLUDING }
{ AM | COMMENTS | CONSTRAINTS | DEFAULTS | ENCODING | GENERATED | IDENTITY
| INDEXES | RELOPT | STATISTICS | STORAGE | ALL }
and <partition_bound_spec> is:
IN ( <partition_bound_expr> [, ...] ) |
FROM ( { <partition_bound_expr> | MINVALUE | MAXVALUE } [, ...] )
TO ( { <partition_bound_expr> | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS <numeric_literal>, REMAINDER <numeric_literal> )
and <index_parameters> in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
[ INCLUDE ( <column_name> [, ... ] ) ]
[ WITH ( <storage_parameter> [=<value>] [, ... ] ) ]
[ USING INDEX TABLESPACE <tablespace_name> ]
and <exclude_element> in an EXCLUDE constraint is:
{ <column_name> | ( <expression> ) } [ <opclass> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
Tables Options
TEMPORARY | TEMP, or IF NOT EXISTS
This syntax is fully supported in Snowflake.
GLOBAL | LOCAL
This syntax is not needed in Snowflake.
According to Greenplum’s documentation, GLOBAL | LOCAL are present for SQL Standard compatibility, but have no effect in Greenplum and are deprecated. For that reason, SnowConvert will remove these keyworks during the migration process.
UNLOGGED tables offer a significant speed advantage by skipping write-ahead logging (WAL). However, their data isn't replicated to mirror instances. Snowflake doesn't support this functionality, so the UNLOGGED clause will be commented out.
Code Example
Input Code:
IN -> Greenplum_02.sql
CREATE UNLOGGED TABLE TABLE1 (
COL1 integer
);
Output Code:
OUT -> Greenplum_02.sql
CREATE
-- --** SSC-FDM-PG0005 - UNLOGGED TABLE IS NOT SUPPORTED IN SNOWFLAKE, DATA WRITTEN MAY HAVE DIFFERENT PERFORMANCE. **
-- UNLOGGED
TABLE TABLE1 (
COL1 integer
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "greenplum", "convertedOn": "04/21/2025", "domain": "test" }}';
Column Attributes
Encoding
This syntax is not needed in Snowflake.
The compression encoding for a column. In Snowflake, defining ENCODING is unnecessary because it automatically handles data compression, unlike Greenplum, which could set up the encoding manually. For this reason, the ENCODING statement is removed during migration.
Grammar Syntax
The CHECK clause specifies an expression producing a Boolean result that new or updated rows must satisfy for an insert or update operation to succeed. Snowflake does not have an equivalence with this clause; SnowConvert will add an EWI. This will be applied as a CHECK attribute or table constraint.
Grammar Syntax
CHECK ( <expression> )
Sample Source
Input Code:
IN -> Greenplum_04.sql
CREATE TABLE table1 (
product_id INT PRIMARY KEY,
quantity INT CHECK (quantity >= 0)
);
SnowConvert keeps the constraint definitions; however, in Snowflake, unique, primary, and foreign keys are used for documentation and do not enforce constraints or uniqueness. They help describe table relationships but don't impact data integrity or performance.
Table Attributes
LIKE option
This syntax is partially supported in Snowflake.
The LIKE clause specifies a table from which the new table automatically copies all column names, their data types, and their not-null constraints. Greenplum supports several options, while Snowflake does not so that SnowConvert will remove the options like.
Grammar Syntax
LIKE source_table { INCLUDING | EXCLUDING }
{ AM | COMMENTS | CONSTRAINTS | DEFAULTS | ENCODING | GENERATED | IDENTITY
| INDEXES | RELOPT | STATISTICS | STORAGE | ALL }
Sample Source Patterns
Input Code:
IN -> Greenplum_06.sql
CREATE TABLE source_table (
id INT,
name VARCHAR(255),
created_at TIMESTAMP,
status BOOLEAN
);
CREATE TABLE target_table_no_constraints (LIKE source_table INCLUDING DEFAULTS EXCLUDING CONSTRAINTS EXCLUDING INDEXES);
Specifies the behaviour of the temporary table when a commit is done.
Grammar Syntax
ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP }
Sample Source Patterns
Input Code:
IN -> Greenplum_07.sql
CREATE GLOBAL TEMPORARY TABLE temp_data_delete (
id INT,
data TEXT
) ON COMMIT DELETE ROWS;
Output Code:
OUT -> Greenplum_07.sql
CREATE TEMPORARY TABLE temp_data_delete (
id INT,
data TEXT
)
----** SSC-FDM-0008 - ON COMMIT NOT SUPPORTED **
--ON COMMIT DELETE ROWS
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "greenplum", "convertedOn": "04/24/2025", "domain": "test" }}';
DISTRIBUTED BY
This syntax is fully supported in Snowflake.
The DISTRIBUTED BY clause in Greenplum controls how table data is physically distributed across the system's segments. Meanwhile, CLUSTER BY is a subset of columns in a table (or expressions on a table) that are explicitly designated to co-locate the data in the table in the same micro-partitions.
Grammar Syntax
DISTRIBUTED BY ( <column> [<opclass>] [, ... ] )
Sample Source Patterns
Input Code:
IN -> Greenplum_07.sql
CREATE TABLE table1 (colum1 int, colum2 int, colum3 smallint, colum4 int )
DISTRIBUTED BY (colum1, colum2);
Output Code:
OUT -> Greenplum_07.sql
CREATE TABLE table1 (colum1 int, colum2 int, colum3 smallint, colum4 int )
--** SSC-FDM-GP0001 - THE PERFORMANCE OF THE CLUSTER BY MAY VARY COMPARED TO THE PERFORMANCE OF DISTRIBUTED BY **
CLUSTER BY (colum1, colum2)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "greenplum", "convertedOn": "03/26/2025", "domain": "test" }}'
;
DISTRIBUTED RANDOMLY - REPLICATED
This syntax is not needed in Snowflake.
The DISTRIBUTED REPLICATED or DISTRIBUTED RANDOMLY clause in Greenplum controls how table data is physically distributed across the system's segments. As Snowflake automatically handles data storage, these options will be removed in the migration.
These clauses in Snowflake are unnecessary because they automatically handle the data storage, unlike Greenplum, which could be set up manually. For this reason, these clauses are removed during migration.
Related EWIs
SSC-FDM-GP0001: The performance of the CLUSTER BY may vary compared to the performance of Distributed By.
