The pgx Files 03: Inserting

14 Aug 2016

Welcome to part three of The pgx Files! We will assume we still have the users table we created in part two.

If I wanted to insert a user from a psql session, I'd just do this:

# insert into users (username, password, first_name, last_name) values ('foo', 'bar', 'Foo', 'Bar');

# select * from users; rollback;
┌──────────────────────────────────────┬──────────┬──────────┬────────────┬───────────┐
│                  id                  │ username │ password │ first_name │ last_name │
├──────────────────────────────────────┼──────────┼──────────┼────────────┼───────────┤
│ 65df43a0-b778-42ef-a6e9-8178af38bb03 │ foo      │ bar      │ Foo        │ Bar       │
└──────────────────────────────────────┴──────────┴──────────┴────────────┴───────────┘
(1 row)

How would we do the same using pgx?

Here's how I'd do it if I feld like supplying the UUID myself.

(Note we're also testing to see if github.com/satori/go.uuid works with the UUID type in PostgreSQL.)

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 {
		fmt.Fprintf(os.Stderr, "Unable to create user mwood: %v\n", err)
		os.Exit(1)
	}
	fmt.Printf("Successfully created user mwood\n")
}

Now let's see if we can create our user:

$ ./insertuser 
Successfully created user mwood

Yup.

How does our user look in the database?

# select * from users;
┌──────────────────────────────────────┬──────────┬──────────┬────────────┬───────────┐
│                  id                  │ username │ password │ first_name │ last_name │
├──────────────────────────────────────┼──────────┼──────────┼────────────┼───────────┤
│ 65df43a0-b778-42ef-a6e9-8178af38bb03 │ foo      │ bar      │ Foo        │ Bar       │
│ 1bc23c85-75f7-4b3e-b17e-d215230f994f │ mwood    │ passwd   │ Manni      │ Wood      │
└──────────────────────────────────────┴──────────┴──────────┴────────────┴───────────┘
(2 rows)

Nice. There's the user we created at the psql prompt, and there's the user we created with our Go program.

Next on The pgx Files: catching the error of inserting a user who is already there, versus handling actual unexpected errors.