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:
- 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.
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.