Upsert Page Counters
Hard · Upsert
INSERT ... ON CONFLICT (key) DO UPDATE SET ... inserts new rows and updates existing ones in one statement. In the DO UPDATE part, EXCLUDED is the row you tried to insert. One rule: a single statement cannot update the same row twice, so combine duplicates in the source first.
Add the visits to page_stats in one statement. For each page in visits: a new page is inserted with views = its number of visits and last_seen = its latest visit. An existing page gets its visits added to views, and last_seen becomes the later of the old value and its latest visit.
Return the changed rows with RETURNING page, views, last_seen plus inserted: true for new pages. Hint: the system column xmax is 0 in RETURNING for a freshly inserted row, so (xmax = 0) AS inserted works.