AnalyticDB for PostgreSQL tables are similar to tables in a relational database, except that table rows are distributed across segments. The distribution of the rows in a table is determined by the distribution policy of the table.

Create a standard table

The CREATE TABLE statement is used to create a table. When you create a table, you can define the following items:
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.
				

Example:

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

Create a temporary table

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}]
Note You can use the ON COMMIT clause to control the operation to be performed on a table at the end of the current transaction.
  • PRESERVE ROWS: Data is retained at the end of the current transaction. This is the default operation.
  • DELETE ROWS: All rows are deleted at the end of the current transaction.
  • DROP: Temporary tables are deleted at the end of the current transaction.

Example:

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;

Define constraints

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 ]
				

CHECK constraint

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

NOT NULL constraint

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

UNIQUE constraint

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

PRIMARY KEY constraint

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

References

For more information, visit Pivotal Greenplum documentation.