Upsert Row
db_upsertInsert a new row or update an existing one using PostgreSQL's ON CONFLICT. Specify a unique key filter, create values for new rows, and optional update values for matches.
Instructions
Inserts a new row or updates an existing one using PostgreSQL's ON CONFLICT mechanism. The 'where' filter's columns must match a unique constraint or primary key on the table — this is how PostgreSQL determines whether to insert or update. If a matching row exists, only the columns specified in 'update' are changed. If no match exists, a new row is created with the values from 'create'. Use dry_run=true to preview.
When to use:
"Create this user if they don't exist, otherwise update their last_login"
"Upsert product SKU-123 with price $29.99"
Idempotent insert-or-update operations
Parameter guidance:
table: the target table name (required)
where: JSON filter identifying the conflict target (required). Columns must match a unique constraint or primary key. Example: {"email": "jane@example.com"}
create: JSON object of column-value pairs for the INSERT case (required)
update: JSON object of column-value pairs for the UPDATE case (optional). If omitted, no update occurs on conflict — the existing row is returned unchanged.
dry_run: set to true to validate without writing (default: false)
Behavioral notes:
The where columns MUST match a unique constraint or primary key — the tool validates this against the schema and returns an error if no matching constraint exists.
Returns the final row (either newly inserted or updated).
This is idempotent — calling it multiple times with the same data has the same effect as calling it once.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| table | Yes | Name of the table to query | |
| where | Yes | JSON filter identifying the row, must match a unique constraint | |
| create | Yes | JSON object of column-value pairs to insert if not found | |
| dryRun | No | If true, simulates without writing | |
| update | No | JSON object of column-value pairs to set if found | |
| database | No | Name of the database to query (from pgautopilot.json). Omit to use the current default database. |