Database → Upsert
AI-generatedSummary
Inserts new rows or updates existing rows in an openGauss table based on a unique column.
Inputs
- schema (required) — The target schema (defaults to 'public').
- table (required) — The target table name.
- columnToMatchOn (required) — The unique column used to determine if a row already exists (e.g., an ID). This column is not updated; it is used only for matching.
- dataMode (required) — Determines how input data is mapped to table columns: 'autoMapInputData' maps incoming properties to columns, or 'defineBelow' to manually configure values per column.
- valueToMatchOn — The specific value of the unique column to match against when in 'defineBelow' mode.
- valuesToSend — A list of column-value pairs to insert or update. Visible only in 'defineBelow' mode.
- options — Optional settings including 'outputColumns' (comma-separated list of columns to return via RETURNING) or 'cascade' (ignored for upsert).
Output shape
A single success record or a list of rows (if RETURNING is used), paired with the input item.
Returns affectedRows count if no RETURNING clause is specified. Errors are returned as an error object if 'Continue on Error' is enabled.
Examples
Example 1: Upsert a user by email
Use 'defineBelow' mode, set columnToMatchOn to 'email', and map valuesToSend to 'id' and 'name'.
Example 2: Bulk upsert with auto-mapping
Use 'autoMapInputData' mode and ensure incoming data fields match the table column names.