INSERT
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