The pgx Files 04: Constraint Violation Detection

14 Aug 2016

Welcome back to The pgx Files! We continue on with the users table we created in part two of The pgx Files.

Trying to insert a user with an already-taken username is an everyday event. Many database drivers will throw an exception or return an error in this case, and the trick is to find out if this is an expected error (user already exists) or an unexpected error (table doesn't exist, insert permission denied, database unavailable, etc.).

In psql, we get a useful error message like so:

# insert into users (id, username, password, first_name, last_name)
             values (default, 'mwood', 'foo', 'Manni', 'Wood');
ERROR:  duplicate key value violates unique constraint "unique_username"
DETAIL:  Key (username)=(mwood) already exists.

Here's how to trap for a violation of our unique_username constraint with pgx, while still acknowledging more serious errors. pgx surfaces all of our useful information through the pgx.PgError type.

package main

import (
	"fmt"
	"os"

	"github.com/jackc/pgx"
	"github.com/manniwood/playground/pgxfiles"
	"github.com/satori/go.uuid"
)

func main() {
	conn := util.Connect("user inserter")
	defer conn.Close()

	id := uuid.NewV4()

	_, err := conn.Exec(`
	insert into users (
    id,
    username,
    password,
    first_name,
    last_name) values ($1, $2, $3, $4, $5);
	`, id, "mwood", "passwd", "Manni", "Wood")
	if err != nil {
		if pgerr, ok := err.(pgx.PgError); ok {
			if pgerr.ConstraintName == "unique_username" {
				fmt.Fprintf(os.Stderr, "Unable to create user mwood, because username already taken: %v\n", pgerr)
			} else {
				fmt.Fprintf(os.Stderr, "Unexpected postgres error trying to create user mwood: %v\n", pgerr)
			}
		} else {
			fmt.Fprintf(os.Stderr, "Unexpected error trying to create user mwood: %v\n", err)
		}
		os.Exit(1)
	}
	fmt.Printf("Successfully created user mwood\n")
}

Now let's see if error handling correctly traps trying to insert the user again:

$ ./insertuser 
Unable to create user mwood, because username already taken: ERROR: duplicate key value violates unique constraint "unique_username" (SQLSTATE 23505)

Yup!

Next on The pgx Files, how to do insert/returning!