INSERT

RETURNING clause
UNION allowed in feeding SELECT

Available in: DSQL, ESQL, PSQL

Changed in: 2.0

Description: Adds rows to a database table, or to one or more tables underlying a view. Field values can be given in the VALUES clause (in which case exactly one row is inserted) or they can come from a SELECT statement.

Syntax:

INSERT [TRANSACTION name]
   INTO {tablename | viewname} [(<columns>)]
   {VALUES (<values>) [RETURNING <columns> [INTO <variables>]]
    | select_expr}

<columns>    ::=  colname  [, colname  ...]
<values>     ::=  value    [, value    ...]
<variables>  ::=  :varname [, :varname ...]
Restrictions

The TRANSACTION directive is only available in ESQL.

The RETURNING clause is not available in ESQL.

The “INTO <variables>” subclause is only available in PSQL.

The trigger context variables OLD and NEW must not be preceded by a colon (“:”).

New in 2.0: No column may appear more than once in the insert list.

RETURNING clause

Available in: DSQL, PSQL

Added in: 2.0

Description: An INSERT query – unless it is SELECT-based – may optionally specify a RETURNING clause to produce a result set containing the values that have been actually stored. The clause, if present, need not contain all of the insert columns and may also contain other columns or expressions. The returned values reflect any changes that may have been made in BEFORE tiggers, but not those in AFTER triggers.

Example:

insert into Scholars (firstname, lastname, address, phone, email)
  values ('Henry', 'Higgins', '27A Wimpole Street', '3231212', null)
  returning lastname, fullname, id

UNION allowed in feeding SELECT

Changed in: 2.0

Description: A SELECT query used in an INSERT statement may now be a UNION.

Example:

insert into Members (number, name)
  select number, name from NewMembers where Accepted = 1
    union
  select number, name from SuspendedMembers where Vindicated = 1