Merging rows

sqlc supports PostgreSQL’s MERGE statement (PostgreSQL 15+). Parameters are inferred in every clause: the source subquery, the join condition, WHEN conditions, UPDATE SET assignments, and INSERT ... VALUES lists.

CREATE TABLE inventory (
  sku        text PRIMARY KEY,
  quantity   integer NOT NULL,
  updated_by text
);

CREATE TABLE inventory_updates (
  sku   text PRIMARY KEY,
  delta integer NOT NULL
);

-- name: SyncInventory :exec
MERGE INTO inventory AS t
USING inventory_updates AS u
ON t.sku = u.sku
WHEN MATCHED AND t.quantity + u.delta <= $1 THEN
    DELETE
WHEN MATCHED THEN
    UPDATE SET quantity = t.quantity + u.delta, updated_by = $2
WHEN NOT MATCHED THEN
    INSERT (sku, quantity, updated_by) VALUES (u.sku, u.delta, $2);

type SyncInventoryParams struct {
	Quantity  int32
	UpdatedBy sql.NullString
}

func (q *Queries) SyncInventory(ctx context.Context, arg SyncInventoryParams) error {
	_, err := q.db.ExecContext(ctx, syncInventory, arg.Quantity, arg.UpdatedBy)
	return err
}

Returning merged rows

On PostgreSQL 17 and later, MERGE supports a RETURNING clause, which can be used with :one or :many. The clause can reference columns from both the target and the source relations.

-- name: MergeInventory :many
MERGE INTO inventory AS t
USING inventory_updates AS u
ON t.sku = u.sku
WHEN MATCHED THEN
    UPDATE SET quantity = u.delta
WHEN NOT MATCHED THEN
    INSERT (sku, quantity) VALUES (u.sku, u.delta)
RETURNING t.sku, t.quantity;

type MergeInventoryRow struct {
	Sku      string
	Quantity int32
}

func (q *Queries) MergeInventory(ctx context.Context) ([]MergeInventoryRow, error) {
	// ...
}

Note that sqlc does not validate the server version: generating code for a MERGE ... RETURNING query succeeds, but running it against a server older than PostgreSQL 17 returns a syntax error at execution time.