Go pq and Postgres appropriate error handling for constraints

If the validation was done correctly, the only thing that can really go wrong is the UNIQUE constraints.

No, the client could lack sufficient privileges, the client might have entered a valid password that's not the right password, the client might have entered a valid voucher that belongs to a different client, etc.

Using "A SERIALIZABLE transaction which checks every single form value prior to insertion" doesn't make sense. Just insert data, and trap errors.

At the very least, your code needs to examine and respond to the C (Code) field, which is always present in the error struct. You don't need to parse the error struct, but you do need to read it.

If you violate a unique constraint, PostgreSQL will return SQL state 23505 in the Code field. It will also return the name of the first constraint that's violated. It doesn't return the column name, probably because a unique constraint can include more than one column.

You can select the column(s) the constraint refers to by querying the information_schema views.

Here's a simple version of your table.

create table test (
  username VARCHAR(255) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  voucher VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL
);

insert into test values ('msherrill', '[email protected]', 'a', 'wibble');

This quick and dirty go program inserts the same row again. It violates every unique constraint.

package main

import (
    "github.com/lib/pq"
    "database/sql"
    "fmt"
    "log"
)

func main() {
    db, err := sql.Open("postgres", "host=localhost port=5435 user=postgres password=xxxxxxxx dbname=scratch sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }

    rows, err := db.Exec("insert into public.test values ('msherrill', '[email protected]', 'a', 'wibble');")
    if err, ok := err.(*pq.Error); ok {
        fmt.Println("Severity:", err.Severity)
        fmt.Println("Code:", err.Code)
        fmt.Println("Message:", err.Message)
        fmt.Println("Detail:", err.Detail)
        fmt.Println("Hint:", err.Hint)
        fmt.Println("Position:", err.Position)
        fmt.Println("InternalPosition:", err.InternalPosition)
        fmt.Println("Where:", err.Where)
        fmt.Println("Schema:", err.Schema)
        fmt.Println("Table:", err.Table)
        fmt.Println("Column:", err.Column)
        fmt.Println("DataTypeName:", err.DataTypeName)
        fmt.Println("Constraint:", err.Constraint)
        fmt.Println("File:", err.File)
        fmt.Println("Line:", err.Line)
        fmt.Println("Routine:", err.Routine)
    }
   fmt.Println(rows)
}

Here's the output.

Severity: ERROR
Code: 23505
Message: duplicate key value violates unique constraint "test_username_key"
Detail: Key (username)=(msherrill) already exists.
Hint: 
Position: 
InternalPosition: 
Where: 
Schema: public
Table: test
Column: 
DataTypeName: 
Constraint: test_username_key
File: nbtinsert.c
Line: 406
Routine: _bt_check_unique

You have the schema, table, and constraint names. You presumably know the database (catalog) name, too. Use these values to select the schema, table, and column names from information_schema views. You're lucky; in this case you need only one view.

select table_catalog, table_schema, table_name, column_name 
from information_schema.key_column_usage
where 
    table_catalog = 'scratch' and          -- Database name
    table_schema = 'public' and            -- value returned by err.Schema
    table_name = 'test' and                -- value returned by err.Table
    constraint_name = 'test_username_key'  -- value returned by err.Constraint
order by constraint_catalog, constraint_schema, constraint_name, ordinal_position;