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 returning values into the context variable NEW, this name must not be preceded by a colon (“:”).

Example

update or insert into Cows (Name, Number, Location)
  values ('Suzie Moo', 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.