RUEN
Package · cdo

Upsert placeholders

upsert() and upsertGroup() take an updateColumns map of column => expression. Inside each expression, two tokens are substituted with driver-specific SQL. This page is the exact substitution reference.

The two tokens

Token Meaning PostgreSQL MySQL / MariaDB
:new the incoming (would-be-inserted) value EXCLUDED.column VALUES(column)
:current the existing row’s value table.column column

Substitution is a literal string replace against the column the expression is assigned to. For updateColumns: ['stock' => ':current + :new'] on table inventory, the generated SET term is:

Driver Result
PostgreSQL stock = inventory.stock + EXCLUDED.stock
MySQL / MariaDB stock = VALUES(stock) + stock

Dialect wrapper

The full statement CDO emits around the SET terms:

Driver updateColumns empty updateColumns provided
PostgreSQL INSERT … ON CONFLICT (cols) DO NOTHING INSERT … ON CONFLICT (cols) DO UPDATE SET …
MySQL / MariaDB INSERT IGNORE INTO … INSERT … ON DUPLICATE KEY UPDATE …

Expression text is verbatim SQL

Only :new and :current are substituted; the rest of each expression is written into the query unchanged. Never build updateColumns keys or expressions from user input. Row values (from the entity) are always bound safely.

Expression patterns

Any SQL expression is valid; tokens are optional. Common ones:

Expression Effect
:new replace with the incoming value
:current + :new add the incoming value onto the current
:current - :new subtract the incoming from the current
GREATEST(:current, :new) keep the larger of the two
LEAST(:current, :new) keep the smaller of the two
COALESCE(:new, :current) use the incoming value, else keep current
NOW() set to the current timestamp (no token)
CASE WHEN :new < :current THEN NOW() ELSE min_price_date END conditional update

Worked example

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

On PostgreSQL this becomes:

sql
INSERT INTO inventory (warehouse_id, product_id, cost, quantity, updated_at)
VALUES (...), (...)
ON CONFLICT (warehouse_id, product_id) DO UPDATE SET
  cost = EXCLUDED.cost,
  quantity = inventory.quantity + EXCLUDED.quantity,
  updated_at = NOW()

Return-value note

upsert() returns the primary key via RETURNING on PostgreSQL only. On MySQL/MariaDB it returns lastInsertId(), which is null when an existing row was updated or ignored rather than inserted. See the Upserts guide for handling this.