The pgx Files 02: Table Creation

14 Aug 2016

Let's make a users table to test with.

Here's how we do it with psql:

$ psql -U postgres -d postgres

Note that I'm creating the uuid-ossp extension in my postgres database because I haven't set that up yet, but I did compile in the uuid-ossp library, because I want to use the UUID type in my users table.

# create extension "uuid-ossp";

# create table users (
    id uuid constraint user_pk primary key default uuid_generate_v4() not null,
    username text constraint unique_username unique not null,
    password text not null,  -- wow, this is totally not secure
    first_name text not null,
    last_name text not null);

# commit;

OK, so that definition works well at the psql command line; now let's drop the table we just created...

# drop table users;

# commit;

... and do the same thing through pgx.

package main

import (
	"fmt"
	"os"

	"github.com/manniwood/playground/pgxfiles"
)

func main() {
	conn := util.Connect("users table maker")
	defer conn.Close()

	_, err := conn.Exec(`
	create table users (
    id uuid constraint user_pk primary key default uuid_generate_v4() not null,
    username text constraint unique_username unique not null,
    password text not null,  -- wow, this is totally not secure
    first_name text not null,
    last_name text not null);
	`)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to create users table: %v\n", err)
		os.Exit(1)
	}
	fmt.Printf("Successfully created users table\n")
}

Let's run after compiling...

$ ./createdb 
Successfully created users table

That seems to have worked nicely.

What does my psql session think?

# \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)

Good stuff.

What if I run createdb again?

$ ./createdb 
Unable to create users table: ERROR: relation "users" already exists (SQLSTATE 42P07)

Excellent.

Next, on The pgx Files... inserting users, and trapping for usernames that already exist!