Creating tables

Tables are the basic building blocks of a relational database. A table can hold multiple rows (i.e., records), with each row having multiple columns and each column holding a single data element (i.e., value). You can query tables to insert data, select (i.e., retrieve) data, and delete data.

Table of contents

Table definition

To create a table, use the CREATE TABLE statement.

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:

cr> CREATE TABLE my_table (
...   first_column integer,
...   second_column text
... );
CREATE OK, 1 row affected (... sec)

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

A table can be dropped (i.e., deleted) by using the DROP TABLE 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:

cr> DROP TABLE IF EXISTS my_table;
DROP OK, 0 rows affected (... sec)

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 column_policy table parameter so that the INSERT, UPDATE, and COPY FROM statements can arbitrarily create new columns as needed (what’s known as a dynamic column policy).

Schemas

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 (
...   pk int primary key,
...   label text,
...   position geo_point
... );
CREATE OK, 1 row affected (... sec)

We can confirm this by looking up this table in the information_schema.tables table:

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

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

  • blob

  • 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 doc schema:

cr> create table my_doc_table (
...   a_column int,
...   another_one geo_point
... );
CREATE OK, 1 row affected (... sec)

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 Lexical structure section for more information about naming.

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

  • may not contain one of the following characters: \ / * ? " < > | <whitespace> , # .

  • should not exceed 255 bytes when encoded with utf-8 (this limit applies on the optionally schema-qualified table name)

Column names are restricted in terms of patterns:

  • Columns are not allowed to contain a dot (.), since this conflicts with internal path definitions.

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

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

Table configuration

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