New in v0.5.0:
CREATE TABLE creates an in-memory table.
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.
You can seamlessly join tables with other tables, views, and sources in the system.
When to use a table
A table can be more convenient than a source, but only if all of the following statements are true:
- Your dataset is either static or append only.
- You do not need the dataset to survive reboots of Materialize.
- Your dataset is several times smaller than the available memory on your machine. See the memory usage section below for details.
If any of those statements do not describe your situation, we recommend that you use a source instead.
|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
Additionally, tables do not currently support:
- Primary keys
- Unique constraints
- Check constraints
- Insert statements that refer to data in other relations, e.g.:
INSERT INTO t1 SELECT * FROM t2
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
Temporary tables may depend upon other temporary database objects, but non-temporary tables may not depend on temporary objects.
Every table is backed by an index that materializes all of the data in the table. Unlike sources, tables cannot be “unmaterialized”. Therefore you must ensure that the data you store in tables fits in the amount of memory you have available on your system. Remember that any additional indexes or derived materialized views will count against your memory budget.
If your dataset is too large to fit in memory, consider using an unmaterialized source instead. This lets you defer materialization to views derived from this source, which can aggregate or filter the data down to a manageable size.
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 TABLES; TABLES ------ t SHOW COLUMNS IN t; name nullable type ------------------------- a true int4 b false text