Hands-On Software Engineering with Golang

Upserting links

To upsert a link to the CockroachDB store, we will use an upsert-like SQL query that leverages the database's support for specifying an action to be applied when a conflict occurs:

INSERT INTO links (url, retrieved_at) VALUES ($1, $2) 
ON CONFLICT (url) DO UPDATE SET retrieved_at=GREATEST(links.retrieved_at, $2)
RETURNING id, retrieved_at

Basically, if we try to insert a link that has the same url as an existing link, the preceding conflict resolution action will ensure that we simply update the retrieved_at column to the maximum of the original value and the one specified by the caller. Regardless of whether a conflict occurs or not, the query will always return the row's id (existing or assigned by the DB) and the value ...