: Documentation: 14: CREATE TABLE : Postgres Professional

CREATE TABLE
Prev	Up	6.5. SQL Commands	Home	Next

CREATE TABLE

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
    | LIKE source_table [ like_option ... ]}
    [, ... ]
] )
[ 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:

Generated columns.
REFERENCES and FOREIGN KEY constraints between non-colocated sharded tables.
PARTITION BY and PARTITION OF clauses.

When creating a colocated table, have in mind the related limitations. Specifically, from these limitations, it follows that a foreign key on a global table can reference only another global table and 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 for the ON UPDATE action and only NO ACTION, RESTRICT or CASCADE are supported for the ON DELETE action.

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

Parameters

IF NOT EXISTS: Do not throw an error if a relation with the same name already exists. A notice is issued in this case. IF NOT EXISTS does not lead to an error if an existing table with the same name is global or sharded, or if it is local yet is located on a node from which the query is run. Otherwise, such a query fails with error.

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 leaf partitions 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, for a sharded table, the value of the global setting of shardman.num_parts will be used, for a colocated table, the value will be taken from the corresponding colocating table.
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 parameter 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

In this example, the table pgbench_branches is created, 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',
      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}}$$
);

These simple examples of CREATE TABLE illustrate limitations related to creation of colocated tables:

This command creates a table to colocate with:

CREATE TABLE teams_players (
       team_id integer NOT NULL,
       player_id integer,
       scores int,
       PRIMARY KEY (team_id, player_id)
) WITH (distributed_by='team_id, player_id');

This command correctly creates a colocated table:

CREATE TABLE players_scores (
       player_id integer NOT NULL,
       team_id integer,
       interval tstzrange,
       scores integer,
       foreign key (team_id, player_id) references teams_players(team_id, player_id)
) WITH (distributed_by='team_id, player_id', colocate_with='teams_players');

And this command contains an error in the definition of a foreign key:

CREATE TABLE players_scores (
       player_id integer NOT NULL,
       team_id integer,
       interval tstzrange,
       scores integer,
       foreign key (team_id, player_id) references teams_players(team_id, player_id)
) WITH (distributed_by='player_id, team_id', colocate_with='teams_players');
ERROR:  foreign key should start with distributed_by columns

Consider another example:

CREATE TABLE teams (team_id integer primary key, team_name text) with (distributed_by='team_id');
CREATE TABLE players_teams (
       player_id integer,
       team_id integer references teams(team_id),
       scores integer
) WITH (distributed_by='player_id', colocate_with='teams');
ERROR:  foreign key should start with distributed_by columns