Creating tables

To create a table, use the CREATE TABLE .

At a minimum, you must specify a table name and one or more column definitions. A column definition must specify a column name and a corresponding data type.

Here’s an example statement:

This statement creates a table named with two columns named first_column and second_column with types and text.

A table can be dropped (i.e., deleted) by using the statement:

cr> DROP TABLE my_table;
DROP OK, 1 row affected (... sec)

If the my_table table did not exist, the DROP TABLE statement above would return an error message. If you specify the IF EXISTS clause, the instruction is conditional on the table’s existence and would not return an error message:

Tip

By default, CrateDB will enforce the column definitions you specified with the CREATE TABLE statement (what’s known as a strict column policy).

However, you can configure the table parameter so that the INSERT, , and COPY FROM statements can arbitrarily create new columns as needed (what’s known as a dynamic column policy).

Tables can be created in different schemas. These are created implicitly on table creation and cannot be created explicitly. If a schema did not exist yet, it will be created.

You can create a table called my_table in a schema called my_schema schema like so:

cr> create table my_schema.my_table (
...   label text,
...   position geo_point
... );

We can confirm this by looking up this table in the table:

The following schema names are reserved and may not be used:

• information_schema

• sys

Tip

Schemas are primarily namespaces for tables. You can use privileges to control access to schemas.

A user-created schema exists as long as there are tables with the same schema name. If the last table with that schema is dropped, the schema is gone (except for the blob and doc schema):

cr> drop table my_schema.my_table ;
DROP OK, 1 row affected (... sec)

Every table that is created without an explicit schema name, will be created in the schema:

cr> select table_schema, table_name from information_schema.tables
... where table_name='my_doc_table';
+--------------+--------------+
| table_schema | table_name   |
+--------------+--------------+
| doc          | my_doc_table |
SELECT 1 row in set (... sec)

Naming restrictions

Table, schema and column identifiers cannot have the same names as reserved key words. Please refer to the section for more information about naming.

Additionally, table and schema names are restricted in terms of characters and length. They:

Column names are restricted in terms of patterns:

• Columns that conflict with the naming scheme of virtual system columns are restricted.

• Character sequences that conform to the (e.g. col['id']) are not allowed.

You can configure tables in many different ways to take advantage of the range of functionality that CrateDB supports. For example:

• CrateDB transparently segments the underlying storage of table data into shards (four by default). You can configure the number of shards with the clause. You control how CrateDB routes table rows to shards by specifying a routing column.

  You can use to configure how shards are balanced across a cluster and to nodes (with attribute-based allocation, , or both).

  See also

  How-to guides: Tuning sharding performance

• You can shards WITH the table setting. CrateDB will split replicated partitions into primary shards, with each primary shard having one or more replica shards.

  When you lose a primary shard (e.g., due to node failure), CrateDB will promote a replica shard to primary. More table replicas mean a smaller chance of permanent data loss (through increased data redundancy) in exchange for more disk space utilization and intra-cluster network traffic.

  Replication can also improve read performance because any increase in the number of shards distributed across a cluster also increases the opportunities for CrateDB to query execution across multiple nodes.

• You can partition a table into one or more partitions with the clause. You control how tables are partitioned by specifying one or more partition columns. Each unique combination of partition column values results in a new partition.

  By partitioning a table, you can segment some (e.g., those used for table optimization, , and backup and restore) by constraining them to one or more partitions.

  See also