Upserts in Go With ON CONFLICT and Returning Clauses

"Insert this row, or update it if it already exists" is one of the most common writes in a service, and the version written as a select followed by an insert or an update has a race: two requests both see no row and both insert, and one fails. PostgreSQL has a single atomic statement for it, INSERT ... ON CONFLICT. This tip shows it from Go on PostgreSQL 18.6, with the return value that tells you which path it took.

The statement

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"log"

	_ "github.com/jackc/pgx/v5/stdlib"
)

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

type Counter struct {
	Key      string
	Hits     int
	Inserted bool
}

// Hit adds one to a counter, creating it at 1, and reports whether the row was new.
func Hit(ctx context.Context, db *sql.DB, key string) (Counter, error) {
	c := Counter{Key: key}
	err := db.QueryRowContext(ctx, `
		insert into counters617 (key, hits) values ($1, 1)
		on conflict (key) do update set hits = counters617.hits + 1
		returning hits, (xmax = 0)`, key).Scan(&c.Hits, &c.Inserted)
	return c, err
}

func main() {
	ctx := context.Background()
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	for _, q := range []string{
		"drop table if exists counters617",
		"create table counters617 (key text primary key, hits int not null)",
	} {
		if _, err := db.ExecContext(ctx, q); err != nil {
			log.Fatal(err)
		}
	}
	for _, k := range []string{"home", "home", "docs", "home"} {
		c, err := Hit(ctx, db, k)
		if err != nil {
			log.Fatal(err)
		}
		fmt.Printf("hit %-5s -> hits=%d inserted=%v\n", c.Key, c.Hits, c.Inserted)
	}

	// do nothing returns no row for a conflict, which database/sql reports as ErrNoRows.
	var hits int
	err = db.QueryRowContext(ctx, "insert into counters617 (key, hits) values ('home', 99) on conflict (key) do nothing returning hits").Scan(&hits)
	fmt.Println("do nothing on an existing key:", errors.Is(err, sql.ErrNoRows))

	// The same statement given the same key twice in one command is an error for do update.
	_, err = db.ExecContext(ctx, "insert into counters617 (key, hits) values ('a', 1), ('a', 2) on conflict (key) do update set hits = excluded.hits")
	fmt.Println("same key twice in one statement:", err)
}

hit home  -> hits=1 inserted=true
hit home  -> hits=2 inserted=false
hit docs  -> hits=1 inserted=true
hit home  -> hits=3 inserted=false
do nothing on an existing key: true
same key twice in one statement: ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time (SQLSTATE 21000)

Hit inserts a counter with hits = 1. If the key already exists, the conflict clause updates the existing row instead, adding one. counters617.hits + 1 refers to the existing value through the table name, and excluded would refer to the row that was proposed for insert. The on conflict (key) target must name a column or set of columns with a unique index or constraint; without one, PostgreSQL rejects the statement.

The output shows the sequence: home came back with hits=1, then 2 and 3 on later calls, and docs started at 1. No read happened in between, and two callers racing on the same key cannot lose an increment, because the row's lock serialises the updates.

Telling insert from update

The second returned column, (xmax = 0), printed inserted=true for the first call on a key and false after. xmax is a system column that is zero for a freshly inserted row version and non zero when the row was updated by the conflict path. It works, and it is an implementation detail of PostgreSQL rather than a documented contract, so use it for logging and metrics, not for logic that must be right across versions. If correctness depends on knowing, run a separate select in the same transaction, or restructure so it does not matter.

DO NOTHING and the missing row

on conflict do nothing returning hits returns a row only when it inserted one. For an existing key the statement succeeds with zero rows, and QueryRowContext(...).Scan reports sql.ErrNoRows, which the lab printed as true. That is not a failure. Treat it as "someone else got there first", and read the row if you need it. The pattern for handling that error without hiding real ones is in ErrNoRows handling.

One error to know

Passing the same key twice in a single do update statement, as in values ('a', 1), ('a', 2), fails with ON CONFLICT DO UPDATE command cannot affect row a second time (SQLSTATE 21000). The statement would update its own freshly inserted row, which is ambiguous. Deduplicate the batch in Go before sending, keeping the last value per key, or use do nothing, which tolerates duplicates. This matters most when upserting in bulk, as in bulk insert with unnest and COPY.

An upsert still needs the unique index to exist, and it takes a lock on the conflicting row until the transaction ends, so keep the transaction short. The clause is documented in the PostgreSQL manual's INSERT page.