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
RETURNING clause causes
INSERT to return values based on each inserted row.
INSERT ... SELECTcan reference user-created tables but not sources (or views, materialized views, and indexes that depend on sources).
- Low performance. While processing an
INSERT ... SELECTstatement, Materialize cannot process other
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
The privileges required to execute this statement are:
USAGEprivileges on the schemas that all relations and types in the query are contained in.
SELECTprivileges on all relations in the query.
- NOTE: if any item is a view, then the view owner must also have the necessary privileges to execute the view definition. Even if the view owner is a superuser, they still must explicitly be granted the necessary privileges.
USAGEprivileges on all types used in the query.
USAGEprivileges on the active cluster.