Creates a new table in PostgreSQL. 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 [ STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT } ] [ COMPRESSION compression_method ] [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option ... ] }
[, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
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 method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
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 method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ 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 [ NULLS [ NOT ] DISTINCT ] 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 [ NULLS [ NOT ] DISTINCT ] ( 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 } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | 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 )
index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]
exclude_element in an EXCLUDE constraint is:
{ column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
referential_action in a FOREIGN KEY/REFERENCES constraint is:
{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] | SET DEFAULT [ ( column_name [, ... ] ) ] }
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 PostgreSQL’s documentation, GLOBAL | LOCAL are present for SQL Standard compatibility, but have no effect in PostgreSQL and are deprecated. For that reason, SnowConvert will remove these keyworks during the migration process.
UNLOGGED tables offer a significant speed advantage because they are not written to the write-ahead log. Snowflake doesn't support this functionality, so the UNLOGGED clause will be commented out.
Code Example
Input Code:
IN -> PostgreSQL_02.sql
CREATE UNLOGGED TABLE TABLE1 (
COL1 integer
);
Output Code:
OUT -> PostgreSQL_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": "postgresql", "convertedOn": "05/09/2025", "domain": "no-domain-provided" }}';
Column Attributes
CHECK Attribute
This syntax is not supported in Snowflake.
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 -> PostgreSQL_03.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. PostgreSQL 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 -> PostgreSQL_05.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 -> PostgreSQL_06.sql
CREATE GLOBAL TEMPORARY TABLE temp_data_delete (
id INT,
data TEXT
) ON COMMIT DELETE ROWS;
Output Code:
OUT -> PostgreSQL_06.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": "postgresql", "convertedOn": "05/12/2025", "domain": "no-domain-provided" }}';
PARTITION BY, USING, TABLESPACE, and WITH
This syntax is not needed in Snowflake.
These clauses in Snowflake are unnecessary because they automatically handle the data storage, unlike PostgreSQL, which could be set up manually. For this reason, these clauses are removed during migration.
