Home  /  SQL commands  /  CREATE TABLE

CREATE TABLE: Read-write table

View as Markdown

Read-write tables let you read (SELECT) and write (INSERT, UPDATE, DELETE) to the table.

Syntax

To create a new read-write table (i.e., users can perform SELECT, INSERT, UPDATE, and DELETE operations):

CREATE [TEMP|TEMPORARY] TABLE [IF NOT EXISTS] <table_name> (
  <column_name> <column_type> [NOT NULL][DEFAULT <default_expr>]
  [, ...]
)
[WITH (
  PARTITION BY (<column_name> [, ...]) |
  RETAIN HISTORY [=] FOR <duration>
)]
;
Syntax element Description
TEMP / TEMPORARY

Optional. If specified, mark the table as temporary.

Temporary tables are:

  • Automatically dropped at the end of the session;
  • Not visible to other connections;
  • 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.
IF NOT EXISTS Optional. If specified, do not throw an error if the table with the same name already exists. Instead, issue a notice and skip the table creation.
<table_name> The name of the table to create. Names for tables must follow the naming guidelines.
<column_name> The name of a column to be created in the new table. Names for columns must follow the naming guidelines.
<column_type> The type of the column. For supported types, see SQL data types.
NOT NULL Optional. If specified, disallow NULL values for the column. Columns without this constraint can contain NULL values.
DEFAULT <default_expr> Optional. If specified, use the <default_expr> as the default value for the column. If not specified, NULL is used as the default value.
WITH (<with_option>[,…])

The following <with_option>s are supported:

Option Description
PARTITION BY (<column> [, ...]) Optional. The column(s) by which Materialize should internally partition the table. The specified column(s) must be a prefix of the upstream table’s columns (i.e., a subset of one or more columns listed at the start of the table’s column definition list). See the partitioning guide for restrictions on valid values and other details.
RETAIN HISTORY <duration> Optional. Private preview. This option has known performance or stability issues and is under active development.
If specified, Materialize retains historical data for the specified duration, which is useful to implement durable subscriptions.
Accepts positive interval values (e.g., '1hr').

Table names and column names

Names for tables and column(s) must follow the naming guidelines.

Known limitations

Tables do not currently support:

  • Primary keys
  • Unique constraints
  • Check constraints

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

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.

Examples

Create a table

The following example uses CREATE TABLE to create a new read-write table mytable with two columns a (of type int) and b (of type text and not nullable):

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

Once a user-populated table is created, you can perform CRUD (Create/Read/Update/Delete) operations on it.

The following example uses INSERT to write two rows to the table:

INSERT INTO mytable VALUES
(1, 'hello'),
(2, 'goodbye')
;

The following example uses SELECT to read all rows from the table:

SELECT * FROM mytable;

The results should display the two rows inserted:

| a | b       |
| - | ------- |
| 1 | hello   |
| 2 | goodbye |
Back to top ↑