Home  /  Reference

INSERT

INSERT writes values to user-defined tables.

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

INSERT INTO table_name AS alias ( col_name , ) VALUES ( expr , ) , query RETURNING * output_expression AS output_name ,
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

The optional RETURNING clause causes INSERT to return values based on each inserted row.

Known limitations

  • INSERT ... SELECT can reference user-created tables but not sources (or views, materialized views, and indexes that depend on sources).
  • Low performance. While processing an INSERT ... SELECT statement, Materialize cannot process other INSERT, UPDATE, or DELETE statements.

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

Privileges

The privileges required to execute this statement are:

  • USAGE privileges on the schemas that all relations and types in the query are contained in.
  • INSERT privileges on table_name.
  • SELECT privileges 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.
  • USAGE privileges on all types used in the query.
  • USAGE privileges on the active cluster.
Back to top ↑