Create and define standard and temporary tables - AnalyticDB

AnalyticDB for PostgreSQL tables work like tables in relational databases, with one key difference: rows are distributed across compute nodes. The distribution policy of each table determines how rows are spread.

Create a standard table

Use CREATE TABLE to define a table. At creation time, you can specify:

Syntax:

CREATE TABLE table_name (
  [ { column_name data_type [ DEFAULT default_expr ]   -- Column definition
     [column_constraint [ ... ]]                        -- Column-level constraint
  ]
     | table_constraint                                 -- Table-level constraint
  ])
  [ WITH ( storage_parameter=value [, ... ] ) ]         -- Storage model
  [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]  -- Distribution key
  [ partition clause]                                   -- Partitioning strategy

Example:

The following example creates a sales table with trans_id as the distribution key and date-based range partitioning.

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 store intermediate data for the duration of a session or transaction. By default, a temporary table is automatically dropped at the end of the session.

Syntax:

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

Use the ON COMMIT clause to control what happens to the table at the end of the current transaction:

Option	Behavior
`PRESERVE ROWS`	Keeps all rows. This is the default.
`DELETE ROWS`	Deletes all rows, but keeps the table structure.
`DROP`	Drops the table entirely.

Example:

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

Define constraints

Constraints restrict the data that can be stored in a table, at either the column or table level.

Before defining constraints, note the following rules:

CHECK constraints can reference only columns in the table on which they are defined.
UNIQUE and PRIMARY KEY constraints must include the distribution key. These constraints are not allowed on append-optimized (AO) or column-oriented tables.
FOREIGN KEY constraints are allowed but not enforced.
Constraints defined on one partition apply to all other partitions. Constraints cannot be scoped to individual partitions.

Syntax:

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 constraints

A CHECK constraint requires column values to satisfy a Boolean expression.

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

NOT NULL constraints

A NOT NULL constraint prevents a column from containing NULL values.

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

UNIQUE constraints

A UNIQUE constraint ensures that values in a column or group of columns are unique across all rows. The table must be hash-distributed, and the constraint columns must include the distribution key.

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

PRIMARY KEY constraints

A PRIMARY KEY constraint combines a UNIQUE constraint and a NOT NULL constraint. The table must be hash-distributed, and the constraint columns must include the distribution key.

When a table has a primary key, the primary key column or columns are used as the distribution key by default.

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