INSERT

New in v0.5.0.

INSERT writes values to user-defined tables.

WARNING! At the moment, tables do not persist any data that is inserted. This means that restarting a Materialize instance will lose any data that was previously stored in a table.

Conceptual framework

You might want to INSERT data into tables when:

Manually inserting rows into Materialize from a non-streaming data source.
Testing Materialize’s features without setting up a data stream.

Syntax

Field	Use
INSERT INTO table_name	The table to write values to.
alias	Only permit references to table_name as alias.
column_name…	Correlates the inserted rows' columns to table_name’s columns by ordinal position, i.e. the first column of the row to insert is correlated to the first named column. If some but not all of table_name’s columns are provided, the unprovided columns receive their type’s default value, or `NULL` if no default value was specified.
expr…	The expression or value to be inserted into the column. If a given column is nullable, a `NULL` value may be provided.
query	A `SELECT` statements whose returned rows you want to write to the table.

Details

Restrictions

Tables do not persist any data that is inserted. This means that restarting a Materialize instance will lose any data that was previously stored in a table.

Examples

To insert data into a table, execute an INSERT statement where the VALUES clause is followed by a list of tuples. Each tuple in the VALUES clause must have a value for each column in the table. If a column is nullable, a NULL value may be provided.

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

INSERT INTO t VALUES (1, 'a'), (NULL, 'b');

SELECT * FROM t;
 a | b
---+---
   | b
 1 | a

In the above example, the second tuple provides a NULL value for column a, which is nullable. NULL values may not be inserted into column b, which is not nullable.

You may also insert data using a column specification.

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

INSERT INTO t (b, a) VALUES ('a', 1), ('b', NULL);

SELECT * FROM t;

 a | b
---+---
   | b
 1 | a

You can also insert the values returned from SELECT statements:

CREATE TABLE s (a text);

INSERT INTO s VALUES ('c');

INSERT INTO t (b) SELECT * FROM s;

SELECT * FROM t;

 a | b
---+---
   | b
   | c
 1 | a