Table of Contents

SQL, PostgreSQL, Postgres, and psql

SQL stands for Stuctured Query Language. It's the most common language for relational database management systems.

A relational database organizes data into relations, which...well, here's how the vocabulary breaks down in practice:

relation = table

field, attribute = column

record, tuple, instance = row

If you're wondering, "Is there some other way to organize data besides tables?" Yes. Although you see tables come up in the other models, they're treated differently — not as relations but as objects or something else.

PostgreSQL is an object-relational database management system. Postgres is just a nickname for PostgreSQL. An object-relational database tries to combine the good parts of the relational and the object-oriented models. For example, you can define your own data types and write simpler code to execute SQL queries.

psql lets you interact with PostgreSQL in a terminal. This is where you'd do SQL things like create tables and make queries. psql has its own commands, apart from SQL. The common psql commands are:

\l : list databases

\d : list relations (tables) in the current database

\d table_name : describe the named table

\c db_name : connect (switch to) the named database

\q : quit psql

And here are some common SQL commands:

Create an empty table:

CREATE TABLE table_name();

Delete a table:

DROP TABLE table_name;

Create a defined table (one of the columns has to be labeled as the PRIMARY KEY):

Restart and make sure there are no postgres processes running in Activity Monitor.

Install PostgreSQL

Skip Homebrew update if you updated recently.

brew update
brew install postgresql

Initialize the database. One person said postgres is the default user account. PostgreSQL says postgres is the PostgreSQL server. Same difference? Who knows. Confusing.

initdb /usr/local/var/postgres

If it says that the directory exists, then remove it first before initializing.

rm -rf /usr/local/var/postgres
initdb /usr/local/var/postgres

Or just install Postgres.app, but you might have to delete the old postgres directory if it already exists.

Start PostgreSQL Server

If the initialization is successful, it'll say to start the database server using:

pg_ctl -D /usr/local/var/postgres -l logfile start

Or (if you haven't already) just install Postgres.app and double-click to start a server. Postgres.app runs on port 5432. There might be some yelling between the app and Terminal if they want to run on the same port or if there's already a server running.

Start psql.

If you want to interact with Postgres at the # prompt (like connect to databases, create tables, list anything, ...):

psql postgres

postgres is the database that was initialized earlier. If you want to connect to a different database when you open psql, then use that database name instead.

After psql starts, you'll see postgres=# as the prompt. When you enter \l to list all the databases — and assuming postgres is the only database that was initialized since installing PostgreSQL — there should be 3 databases listed: postgres, template0, template1.

If you get error messages about a role (user) or a database not existing...

Create User

To create a superuser that can create databases:

createuser -P -s -e -d some_username

-P will prompt you for a password for the new user. -s makes the new user a superuser. -e echoes the commands that createuser generates and sends them to the server (I don't know what this means). -d allows the new user to create databases. See PostgreSQL Documentation for more details.

Create Database

To create a database and assign it to an owner (ideally one with POWAH!):

createdb some_database -O some_username

-O assigns a user as the owner of the database. Add -w if you want don't want a password required.

The column names listed in the parentheses are the columns in table you're copying into, not the column names in the data file you're copying from. In other words, COPY pets(name, age, type) is short for "copy into the columns name, age, type in the table pets".

You don't need HEADER if the csv file doesn't include a header row.

You also don't need to write out all the column names if there is a one-to-one match between the columns in the table and the columns in the file. In our pets example, we wrote out the column names because there is a mismatch: the table includes an id column, while the csv file doesn't.

To get the familiar kind of output that aggregates data into groups (instead of showing every record in the group), SELECT and GROUP BY the column you want to collapse into groups. You can also get the counts for each group if you run the COUNT function.

SELECT type, COUNT(id) AS NumberOfPets FROM pets GROUP BY type ORDER BY NumberOfPets DESC;