Create and manage tables

The CREATE TABLE statement is used to create a table. When you create a table, you can define the following items:

• Columns of the table and their data types. For more information about data types, see Data types.
• Constraints of the table.
• Define table distribution
• Storage model of the table.
• Partitioning strategy of the table.

Execute the CREATE TABLE statement to create a table. The syntax is as follows:

CREATE TABLE table_name ( 
[ { column_name data_type [ DEFAULT default_expr ]   -- Defines a column for the table.
   [column_constraint [ ... ]                        -- Defines a constraint for the column of the table.
] 
   | table_constraint                                -- Defines a constraint for the table.                            
   ])
   [ WITH ( storage_parameter=value [, ... ] )       -- Defines a storage model for the table.
   [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]  -- Defines a distribution key for the table.          
   [ partition clause]                               -- Defines a partitioning strategy for the table.
				

In this example, trans_id is used as the distribution key, and date-based range partitioning is specified.

CREATE TABLE sales (
  trans_id int,
  date date, 
  amount decimal(9,2), 
  region text)
  DISTRIBUTED BY (trans_id)  
  PARTITION BY RANGE(date)    
  (start (date '2018-01-01') inclusive
   end (date '2019-01-01') exclusive every (interval '1 month'),
   default partition outlying_dates); 
				

Temporary tables are automatically deleted at the end of a session or deleted at the end of the current transaction based on user-defined configuration. Temporary tables are used to store temporary, intermediate data. The statement to create a temporary table is as follows:

CREATE TEMPORARY TABLE table_name(...)
    [ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP}]

Create a temporary table that is to be deleted at the end of the current transaction.

CREATE TEMPORARY TABLE temp_foo (a int, b text) ON COMMIT DROP;

You can define table or column constraints to restrict data in your tables, but there are the following limits:

• CHECK constraints can only reference columns in the table on which the constraints are defined.
• UNIQUE and PRIMARY KEY constraints must cover the distribution key. Such constraints are not allowed on append-optimized or column-oriented tables.
• FOREIGN KEY constraints are allowed but not enforced.
• Constraints that you define on a partition are also used for the other partitions. You cannot define constraints for individual partitions of a table.

The syntax is as follows:

UNIQUE ( column_name [, ... ] )
   | PRIMARY KEY ( column_name [, ... ] ) 
   | CHECK ( expression )
   | FOREIGN KEY ( column_name [, ... ] )
            REFERENCES table_name [ ( column_name [, ... ] ) ]
            [ key_match_type ]
            [ key_action ]
            [ key_checking_mode ]
				

A CHECK constraint allows you to specify that the value in a specific column must satisfy a Boolean expression. Example:

CREATE TABLE products
            ( product_no integer,
              name text,
              price numeric CHECK (price > 0) );
				

A NOT NULL constraint allows you to specify that a column cannot contain NULL values. Example:

CREATE TABLE products
       ( product_no integer NOT NULL,
         name text NOT NULL,
         price numeric );
				

A UNIQUE constraint ensures that the data contained in a column or a group of columns in a table is unique among all the rows in the table. The table that contains a UNIQUE constraint must be hash-distributed, and the constraint columns must contain the distribution key. Example:

CREATE TABLE products
       ( product_no integer UNIQUE,
         name text,
         price numeric)
      DISTRIBUTED BY (product_no);
				

A PRIMARY KEY constraint is a combination of a UNIQUE constraint and a NOT NULL constraint. The table on which a PRIMARY KEY constraint is defined must be hash-distributed, and the constraint columns must contain the distribution key. By default, if a table has a primary key, the column or a group of columns that correspond to the primary key are used as the distribution key for the table. Example:

CREATE TABLE products
       ( product_no integer PRIMARY KEY,
         name text,
         price numeric)
      DISTRIBUTED BY (product_no);