Materialize Logo

INSERT

New in v0.5.0.

INSERT inserts values into a table.

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

INSERT statements insert data into tables. You may want to INSERT data into a table when:

Syntax

INSERT INTO table_name VALUES ( col_value , ) ,
Field Use
table_name The name of the target table.
col_value The value to be inserted into the column. If a given column is nullable, a NULL value may be provided.

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.

INSERT currently only supports a VALUES clause. You cannot use other clauses, such as INSERT INTO ... SELECT or INSERT INTO .. DEFAULT VALUES with INSERT.

Examples

Inserting data into a table

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