Overview

This is an introduction intended to get a Mac OS X user off the ground and running locally with PostgreSQL. Included is how to install postgres, connect to the psql interactive terminal, set up a new user/password, create and delete databases, as well as how to create, update, and delete tables and table content.

Getting Started

Let’s download PostgreSQL on your machine.

Mac OS X:: I highly suggest installing homebrew for current and future use:

$ ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
$ brew update
$ brew install postgres
source

Tutorial

Once you have installed PostgreSQL on your machine, you should be able to access the psql interactive terminal by simply typing:

$ psql

And to exit the shell:

postgres=# \q

You are given a user by default, typically named postgres. If exited, reenter the postgres interactive terminal:

$ psql

And once in the shell, list your postgres users:

postgres=# SELECT * FROM pg_user;

Press q to exit.

Create new user with password authentication

From OS X terminal:

$ CREATEUSER -d -l -P -s yourNewUsername

And enter password.

OR:

$ CREATEUSER --interactive

And enter role (equivalent to yourNewUsername), superuser: y, and password.

Once you have created a new user with a password, lets create a database with the same name as your username (user/role) so we can login to it.

From the Mac OS X terminal:

$ createdb sameAsUsernameJustMade

Or from the psql interactive terminal:

postgres=# CREATE DATABASE sameAsUsernameJustMade;
postgres=# \q

Now, login as yourNewUsername from Mac OS X terminal:

$ psql -U yourNewUsername

And you should see yourNewUsername as the current user in place of what it was before (again, typically postgres or Home).

yourNewUsername=# SELECT * FROM pg_user;

And we should see both our default user and new user. Press q to exit list.

Create database

Let’s create a database in yourNewUsername psql shell:

$ psql -U yourNewUsername
yourNewUsername=# CREATE DATABASE sampleDB;

List all databases:

yourNewUsername=# \l

Under name should be sampleDB and the Owner should be yourNewUsername. Similarly, the db you created with the same username as yourNewUsername should have the Owner of the default user provided upon installation.

To check what you’re connected to at any given time:

yourNewUsername=# \c

To connect to a different database:

yourNewUsername=# \c nameOfDatabase

Drop database

From the Mac OS X terminal:

$ dropdb nameOfDatabase

Or from the psql interactive terminal:

postgres=# DROP DATABASE nameOfDatabase;

Create table

Let’s connect to our sampleDB database and create two new tables; ‘users’ with usernames and passwords, and ‘comments’ with a user_id (reference to an id in ‘users’) and content. Both will haveas an id that will be a serial primary key, meaning each id will be unique and non-recurring:

yourNewUserName=# \c sampleDB
sampleDB=# CREATE TABLE users (id serial primary key, username varchar(60), password varchar(60));

A success message will apper, CREATE TABLE, once entered. Now for the ‘comments’ table:

sampleDB=# CREATE TABLE comments (id serial primary key, user_id integer references users(id) on delete cascade, content varchar(200));

Now to view our tables within the sampleDB:

sampleDB=# \dt

Update table

Add rows

To insert three rows into the ‘users’ table:

sampleDB=# INSERT INTO users VALUES ( default, 'a username', 'a password'), ( default, 'another username', 'another password'), ( default, 'a third username', 'a third password');

Where the order of values within the parentheses correspond to the columns of the given table.

To insert two rows into the ‘comments’ table:

sampleDB=# INSERT INTO comments VALUES ( default, (SELECT id FROM users WHERE username = 'a username'), 'some comment'), ( default, (SELECT id FROM users WHERE username = 'a third username'), 'some comment');

Delete rows

To delete the row in ‘users’ where id = 2 (i.e. the second row):

sampleDB=# DELETE FROM users WHERE id = 2;

To delte the row in ‘users’ where username = ‘a username’:

sampleDB=# DELETE FROM users WHERE username = 'username';

To delete the row in ‘comments’ where user_id = 3:

sampleDB=# DELETE FROM comments WHERE user_id = 3;

Dop table

For the ‘users’ table:

sampleDB=# DROP TABLE IF EXISTS users cascade;

For the ‘comments’ table:

sampleDB=# DROP TABLE IF EXISTS table cascade;

Cascade is only necessary if there are references from one table to another. If ‘comments’ didnt exist we could drop ‘users’ by simply:

sampleDB=# DROP TABLE IF EXISTS users;