The INSERT statement inserts new data into a table.

Examples

-- insert the values (1), (2), (3) into "tbl"
INSERT INTO tbl VALUES (1), (2), (3);
-- insert the result of a query into a table
INSERT INTO tbl SELECT * FROM other_tbl;
-- insert values into the "i" column, inserting the default value into other columns
INSERT INTO tbl(i) VALUES (1), (2), (3);
-- explicitly insert the default value into a column
INSERT INTO tbl(i) VALUES (1), (DEFAULT), (3);

Syntax

INSERT INTO inserts new rows into a table. One can insert one or more rows specified by value expressions, or zero or more rows resulting from a query.

The target column names can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order. The values supplied by the VALUES clause or query are associated with the column list left-to-right.

Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or NULL if there is none.

If the expression for any column is not of the correct data type, automatic type conversion will be attempted.

Returning Clause

The RETURNING clause may be used to return the contents of the rows that were inserted. This can be useful if some columns are calculated upon insert. For example, if the table contains an automatically incrementing primary key, then the RETURNING clause will include the automatically created primary key. This is also useful in the case of generated columns.

Some or all columns can be explicitly chosen to be returned and they may optionally be renamed using aliases. Arbitrary non-aggregating expressions may also be returned instead of simply returning a column. All columns can be returned using the * expression, and columns or expressions can be returned in addition to all columns returned by the *.

-- A basic example to show the syntax
CREATE TABLE t1(i INT);
INSERT INTO t1 
    SELECT 1 
    RETURNING *;

-- A more complex example that includes an expression in the RETURNING clause
CREATE TABLE t2(i INT, j INT);
INSERT INTO t2 
    SELECT 2 as i, 3 as j 
    RETURNING *, i * j as i_times_j;

This example shows a situation where the RETURNING clause is more helpful. First, a table is created with a primary key column. Then a sequence is created to allow for that primary key to be incremented as new rows are inserted. When we insert into the table, we do not already know the values generated by the sequence, so it is valuable to return them. For additional information, see the Create Sequence documentation.

CREATE TABLE t3(i INT PRIMARY KEY, j INT);
CREATE SEQUENCE 't3_key';
INSERT INTO t3 
    SELECT nextval('t3_key') as i, 42 as j 
    UNION ALL
    SELECT nextval('t3_key') as i, 43 as j
    RETURNING *;