Materialize Documentation
Join the Community github/materialize

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:

Syntax

INSERT INTO table_name AS alias ( col_name , ) VALUES ( expr , ) , query
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
Did this info help?
Yes No