CREATE TABLE

CREATE TABLE
Prev	Up	9.3. 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' ],
         [ partition_by = 'column_name' ],
         [ partition_bounds = 'array_of_partition_bound_exprs' ]] |
         [ global ]
) ]

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:

Serial pseudo-types other than SERIAL8 and BIGSERIAL.
Generated columns.
REFERENCES and FOREIGN KEY constraints between non-colocated sharded tables.
PARTITION BY and PARTITION OF clauses.

A foreign key on a global or a sharded table can only be supported if it references tuples, which are stored in the same replication group. This leads to the following restrictions. A foreign key on a global table can reference only another global table. A foreign key on a sharded table can reference a colocated sharded table or a global table. Note that when a foreign key on a sharded or a global table references a global table, only NO ACTION or RESTRICT referential actions are supported.

Columns of SERIAL8 type are implemented using an automatically created global sequence, so all global sequence properties also apply here. (See Section 2.1.7 for details.)

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. Regular storage parameters are transparently passed to table partitions.

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.
partition_by (text): This specifies the name of the column to use for the second level table partitioning. Only range partitioning is currently supported. When this option is used, each table partition is created as a partitioned table. Subpartitions can be created immediately if partition_bounds parameter is set. This parameter is optional.
partition_bounds (text): This sets bounds of second level table partitions. Bounds should be a string representation of a two-dimensional array. Each array member is a pair of a lower and upper bound for partitions. If lower and upper bounds are both NULL, the default partition is created. Number of partitions is determined by the first array dimension. This parameter is optional.
global (boolean): This defines that the table is global. If set, the table will be distributed on all replication groups and will be synchronized by triggers. This parameter is optional.

Examples

Create table pgbench_branches, as well as colocated tables pgbench_accounts and pgbench_history. Each partition of the pgbench_history table is additionally subpartitioned by range.

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');
CREATE TABLE public.pgbench_history (
            tid integer,
            bid integer,
            aid integer,
            delta integer,
            mtime timestamp without time zone,
            filler character(22)
        )
WITH (distributed_by = 'bid',
      num_parts = 8,
      colocate_with = 'pgbench_branches',
      partition_by = 'mtime',
      partition_bounds =
          $${{minvalue, '2021-01-01 00:00'},{'2021-01-01 00:00', '2022-01-01 00:00'},{'2022-01-01 00:00', maxvalue}}$$
);