SQL Dialects & Portability

Pagination, Upsert, and Returning Differences

Pagination, conflict-aware writes, and modified-row retrieval differ in both syntax and concurrency contract. Require a unique stable order, identify the exact uniqueness arbiter and state transition, and consume statement-produced results rather than reconstructing them afterward.

Pagination without a total order is undefined.
Keyset predicates encode the order.
Upsert is not one portable operation.
Concurrency semantics need a constraint.
Affected rows are not a universal outcome API.
Return values in the modifying statement.

Stable first page

SELECT order_id, placed_at, status FROM orders
ORDER BY placed_at DESC, order_id DESC LIMIT 20;

Conflict-aware customer write and result path

INSERT INTO customers (email, display_name) VALUES (:email, :name)
ON CONFLICT (email) DO UPDATE SET display_name = EXCLUDED.display_name
RETURNING customer_id, email, display_name;

Outcome contract to test
Case	Required assertion
two sessions insert same email	one logical customer; loser follows declared conflict path
existing value unchanged	business result independent of affected-row convention
several unique constraints conflict	declared arbiter or explicit rejection; no arbitrary target
trigger/generated column changes row	returned/re-read result reflects stored row
page boundary receives new row	document snapshot or freshness behavior; no claim of offset stability
cursor has duplicate sort key	unique tiebreaker prevents omission