New in v0.5.0.
INSERT writes values to user-defined tables.
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.
|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
|expr…||The expression or value to be inserted into the column. If a given column is nullable, a
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.
To insert data into a table, execute an
INSERT statement where the
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
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
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