The pgx Files 05: Insert/Returning

14 Aug 2016

Welcome back to The pgx Files! Today, we continue using the users table we created in part two, but we truncate it pretty much immediately in today's episode so that we can start fresh.

What if I wanted the database to create the uuid of a new user for me, and return it when I create the user? Let's do it in psql first.

First, let's start fresh:

# truncate table users;

Let's remind ourselves what our user table looks like. In particular, note that we have auto-creation of a UUID for new users:

# \d users
      Table "public.users"
┌────────────┬──────┬─────────────────────────────────────┐
│   Column   │ Type │              Modifiers              │
├────────────┼──────┼─────────────────────────────────────┤
│ id         │ uuid │ not null default uuid_generate_v4() │
│ username   │ text │ not null                            │
│ password   │ text │ not null                            │
│ first_name │ text │ not null                            │
│ last_name  │ text │ not null                            │
└────────────┴──────┴─────────────────────────────────────┘
Indexes:
    "user_pk" PRIMARY KEY, btree (id)
    "unique_username" UNIQUE CONSTRAINT, btree (username)

OK, now let's insert our user and get psql to return us the freshly-created uuid.

# insert into users (username, password, first_name, last_name)
             values ('manni', 'foo', 'Manni', 'Wood') returning id;
┌──────────────────────────────────────┐
│                  id                  │
├──────────────────────────────────────┤
│ 014815e9-beb8-43a1-9f76-ada90bcb18c0 │
└──────────────────────────────────────┘
(1 row)

INSERT 0 1

Nice. Now let's truncate the user's table again so we can start fresh trying the same thing from Go.

# truncate table users;

How easy is it to create a new users and get the UUID using pgx?

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()

	var id uuid.UUID

	err := conn.QueryRow(`
	insert into users (username, password, first_name, last_name)
             values ('manni', 'foo', 'Manni', 'Wood') returning id;`).Scan(&id)

	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 manni with id %v\n", id)
}

Here is our first attempt to run our program

$ ./insertuser2 
Successfully created user manni with id 3a6d8008-fec2-412d-acc6-2767a950c5e7

Success: we created a user and got back the uuid!

Next on The pgx Files, fetching a user!