Postgres tutorial – installing with brew + basics (old)

Updated and vastly improved version (July 8th 2024): link

Step 1: Install postgres

brew install postgres

Step 2: Start postgres server

brew services start postgresql

Step 3: Create a database

Lets call our database “book”

$ createdb book

Step 4: open the psql shell

List all databases in terminal with: $ psql --list

Adding the name of the db after the command psql will open the shell to that db

$ psql book

Your psql shell has different command. For the rest of the article, we will be using the psql shell.

List your databases in psql shell with: \l

* When checking the docs make sure you check know your postgres version with: select version();

Step 5: Create a new table

list your current database tables with: \dt

Create format:

CREATE TABLE <table_name> (<field_name>, <field_type>);

Lets create fields for “body”, “date” and “code”:

CREATE TABLE dids (body text, date timestamp, code text);

Inspect your table schema with: \d <table_name>, e.g. \d dids

Step 6: Add some table rows

Remember: SINGLE QUOTES ONLY and remember the ending semi colon

INSERT INTO dids (body, date)

  VALUES ('paired with Corey', NOW());

INSERT INTO dids (body, date)

  VALUES ('remembered that SQL is SINGLE QUOTES ONLY', NOW());

 

OOPS… – we realize here that our table does not have a primary key

Step 7: Alter your table columns (Schema migration)

Your tables will likely evolve throughout time – you may want to add fields to you table, or like above, we forget to add a primary key.

Add a column for “primary key” that auto increments

ALTER TABLE dids ADD COLUMN id serial primary key;

look at the table schema with: \d <table_name>

Step 8: Query your new database!

select * from dids

 

Installed with:

Homebrew 1.7.1

postgres (PostgreSQL) 10.4