RUEN
Package · cdo

Upserts

An upsert inserts a row, or updates the existing one when a unique conflict occurs. CDO writes the right dialect for you — ON CONFLICT on PostgreSQL, ON DUPLICATE KEY UPDATE on MySQL/MariaDB — and gives you two portable tokens, :new and :current, to describe the update.

Insert-or-ignore

Pass the conflict columns and no update columns to skip existing rows:

php
// PostgreSQL: ON CONFLICT (email) DO NOTHING
// MySQL/MariaDB: INSERT IGNORE
$cdo->upsert('users',
  ['email' => 'alice@example.com', 'name' => 'Alice'],
  conflictColumns: ['email']
);

conflictColumns must name the column(s) that define uniqueness. Passing an empty array throws a CDOException.

Insert-or-update

Add updateColumns — a map of column => expression — to update on conflict. Expressions use two placeholders:

Token Means PostgreSQL MySQL / MariaDB
:new the incoming value EXCLUDED.column VALUES(column)
:current the existing row value table.column column
php
$cdo->upsert('products',
  ['sku' => 'ABC-001', 'name' => 'Widget', 'price' => 9.99, 'stock' => 50],
  conflictColumns: ['sku'],
  updateColumns: [
      'name'  => ':new',              // replace with incoming value
      'price' => ':new',
      'stock' => ':current + :new',   // accumulate onto the existing value
  ]
);

An expression that references neither token is emitted verbatim, so SQL functions work directly:

php
updateColumns: [
  'quantity'   => ':current + :new',
  'updated_at' => 'NOW()',
]

updateColumns is not bound

The column => expression strings are written into the SQL as-is (only :new / :current are substituted). Never build them from user input. Row values are still bound safely; only the expression text is verbatim. See the upsert placeholders reference for the full substitution rules.

Batch upsert

upsertGroup() upserts many rows with chunking, defaulting to 500 rows per statement:

php
$cdo->upsertGroup('inventory', $items,
  conflictColumns: ['warehouse_id', 'product_id'],
  updateColumns: [
      'cost'       => ':new',
      'quantity'   => ':current + :new',
      'updated_at' => 'NOW()',
  ],
  chunkSize: 500
);

Like insertGroup, an empty input array is a no-op; an empty conflictColumns throws.

Return values

  • upsert() returns the primary key only on PostgreSQL (via RETURNING). On MySQL/MariaDB it returns lastInsertId(), which is null when no new row was inserted (i.e. an existing row was updated or ignored).
  • upsertGroup() returns nothing.

Portability of the returned id

Because RETURNING on upsert is PostgreSQL-only here, don’t rely on upsert()’s return value for a stable id across drivers. If you need the id on MySQL/MariaDB, read it back with a follow-up query keyed on your conflict columns.

Common expression patterns

Goal Expression
Replace with new value :new
Add to current :current + :new
Keep the larger GREATEST(:current, :new)
Keep the smaller LEAST(:current, :new)
New value or keep current COALESCE(:new, :current)
Stamp the change time NOW()