CREATE TABLE

CREATE TABLE
Prev	Up	3.2. SQL Commands	Home	Next

CREATE TABLE — define a new table

Synopsis

CREATE [ UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
] )
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ TABLESPACE tablespace_name ]

CREATE TABLE table_name ( [
  { column_name data_type }
    [, ... ]
] )
[ WITH ( [ distributed_by = 'column_name' ],
         [ num_parts = number_of_partitions ],
         [ colocate_with = 'colocation_table_name' ]
) ]

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
  UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Description

Shardman extension of the CREATE TABLE syntax enables creation of sharded tables distributed across all replication groups with a single DDL statement.

The extended CREATE TABLE syntax imposes limitations on the general syntax of the command. For example, there is currently no support for:

All serial types.
Generated columns.
REFERENCES and FOREIGN KEY constraints.
PARTITION BY and PARTITION OF clauses.

Parameters

Storage Parameters

Shardman extends storage parameters of tables with its own storage metaparameters. They are not stored in the corresponding catalog entry, but are used to tell the Shardman extension to perform some additional actions. Mind that in the extended syntax, you cannot use regular storage parameters since PostgreSQL does not support specifying storage parameters for partitioned tables.

distributed_by (text): This specifies the name of the column to use for the table partitioning. Only hash partitioning is currently supported, so this is effectively an equivalent of PARTITION BY HASH, but all the partition leaves will be created immediately on all replication groups and the table will be registered in the Shardman metadata.
num_parts (integer): This sets the number of partitions that will be created for this table. This parameter is optional. If it is not specified, the value of the global setting shardman.num_parts will be used by default.
colocate_with (text): This specifies the name of the table to colocate with. If set, Shardman will try to place partitions of the created table with the same partition key on the same nodes as colocation_table_name. This parameter is optional.

Examples

Create table pgbench_branches and colocated table pgbench_accounts:

CREATE TABLE pgbench_branches (
       bid integer NOT NULL PRIMARY KEY,
       bbalance integer,
       filler character(88)
)
WITH (distributed_by = 'bid',
      num_parts = 8);
CREATE TABLE pgbench_accounts (
       aid integer NOT NULL,
       bid integer,
       abalance integer,
       filler character(84),
       PRIMARY KEY (bid, aid)
)
WITH (distributed_by = 'bid',
      num_parts = 8,
      colocate_with = 'pgbench_branches');