Description

INSERT 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; or the first N column names, if there are only
N columns supplied by the
VALUES clause or query. The values supplied by the
VALUES clause or query are associated with the explicit or
implicit 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.

The optional RETURNING clause causes
INSERT to compute and return value(s)
based on each row actually inserted. This is primarily useful for
obtaining values that were supplied by defaults, such as a serial
sequence number. However, any expression using the table's
columns is allowed. The syntax of the RETURNING list is identical to that of the output
list of SELECT.

You must have INSERT privilege on a
table in order to insert into it. If a column list is specified,
you only need INSERT privilege on the
listed columns. Use of the RETURNING
clause requires SELECT privilege on all
columns mentioned in RETURNING. If you
use the query clause to insert
rows from a query, you of course need to have SELECT privilege on any table or column used in
the query.

Parameters

with_query

The WITH clause allows you to
specify one or more subqueries that can be referenced by
name in the INSERT query. See
Section 7.8 and SELECT for details.

It is possible for the query (SELECT statement) to also contain a
WITH clause. In such a case both
sets of with_query can be
referenced within the query, but the second one takes
precedence since it is more closely nested.

table_name

The name (optionally schema-qualified) of an existing
table.

column_name

The name of a column in the table named by table_name. The column name can be
qualified with a subfield name or array subscript, if
needed. (Inserting into only some fields of a composite
column leaves the other fields null.)

DEFAULT VALUES

All columns will be filled with their default
values.

expression

An expression or value to assign to the corresponding
column.

DEFAULT

The corresponding column will be filled with its default
value.

query

A query (SELECT statement) that
supplies the rows to be inserted. Refer to the SELECT statement for a description of
the syntax.

output_expression

An expression to be computed and returned by the
INSERT command after each row is
inserted. The expression can use any column names of the
table named by table_name.
Write * to return all columns of
the inserted row(s).

output_name

A name to use for a returned column.

Outputs

On successful completion, an INSERT
command returns a command tag of the form

INSERT oidcount

The count is the number of
rows inserted. If count is
exactly one, and the target table has OIDs, then oid is the OID assigned to the inserted row. Otherwise
oid is zero.

If the INSERT command contains a
RETURNING clause, the result will be
similar to that of a SELECT statement
containing the columns and values defined in the RETURNING list, computed over the row(s) inserted
by the command.

Compatibility

INSERT conforms to the SQL standard,
except that the RETURNING clause is a
PostgreSQL extension, as is the
ability to use WITH with INSERT. Also, the case in which a column name list
is omitted, but not all the columns are filled from the
VALUES clause or query, is disallowed by the standard.

Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use
this form
to report a documentation issue.