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 triggers, 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