UPDATE OR INSERT

Available in: DSQL, PSQL

Added in: 2.1

Description: UPDATE OR INSERT checks if any existing records already contain the new values supplied for the MATCHING columns. If so, those records are updated. If not, a new record is inserted. In the absence of a MATCHING clause, matching is done against the primary key. If a RETURNING clause is present and more than one matching record is found, an error is raised.

Syntax:

UPDATE OR INSERT INTO
   {tablename | viewname} [(<columns>)]
   VALUES (<values>)
   [MATCHING (<columns>)]
   [RETURNING <values> [INTO <variables>]]

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

No column may appear more than once in the update/insert column list.

If the table has no PK, the MATCHING clause becomes mandatory.

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

When values are returned into the context variable NEW, this name must not be preceded by a colon (“:”).

Example:

update or insert into Cows (Name, Number, Location)
  values ('Suzy Creamcheese', 3278823, 'Green Pastures')
  matching (Number)
  returning rec_id into :id;

Notes:

Matches are determined with IS NOT DISTINCT, not with the “=” operator. This means that one NULL matches another.
The optional RETURNING clause:
- ...may contain any or all columns of the target table, regardless if they were mentioned earlier in the statement, but also other expressions.
- ...may contain OLD and NEW qualifiers for field names; by default, the new field value is returned.
- ...returns field values as they are after the BEFORE triggers have run, but before any AFTER triggers.