Home  /  Reference

CREATE TABLE

CREATE TABLE defines a table that is persisted in durable storage and can be written to, updated and seamlessly joined with other tables, views or sources.

Tables in Materialize are similar to tables in standard relational databases: they consist of rows and columns where the columns are fixed when the table is created but rows can be added to at will via INSERT statements.

WARNING! At the moment, tables have many known limitations. In most situations, you should use sources instead.

Syntax

CREATE TEMP TEMPORARY TABLE table_name ( col_name col_type col_option , ) with_options

col_option

NOT NULL DEFAULT expr

with_options

WITH ( RETAIN HISTORY = FOR retention_period )
Field Use
TEMP / TEMPORARY Mark the table as temporary.
table_name A name for the table.
col_name The name of the column to be created in the table.
col_type The data type of the column indicated by col_name.
NOT NULL Do not allow the column to contain NULL values. Columns without this constraint can contain NULL values.
default_expr A default value to use for the column in an INSERT statement if an explicit value is not provided. If not specified, NULL is assumed.
retention_period Private preview. This option has known performance or stability issues and is under active development. Duration for which Materialize retains historical data, which is useful to implement durable subscriptions. Accepts positive interval values (e.g. '1hr'). Default: 1s.

Details

Known limitations

Tables do not currently support:

  • Primary keys
  • Unique constraints
  • Check constraints

See also the known limitations for INSERT, UPDATE, and DELETE.

Temporary tables

The TEMP/TEMPORARY keyword creates a temporary table. Temporary tables are automatically dropped at the end of the SQL session and are not visible to other connections. They are always created in the special mz_temp schema.

Temporary tables may depend upon other temporary database objects, but non-temporary tables may not depend on temporary objects.

Examples

Creating a table

You can create a table t with the following statement:

CREATE TABLE t (a int, b text NOT NULL);

Once a table is created, you can inspect the table with various SHOW commands.

SHOW TABLES;
TABLES
------
t

SHOW COLUMNS IN t;
name       nullable  type
-------------------------
a          true      int4
b          false     text

Privileges

The privileges required to execute this statement are:

  • CREATE privileges on the containing schema.
  • USAGE privileges on all types used in the table definition.
  • USAGE privileges on the schemas that all types in the statement are contained in.
Back to top ↑