Lesson Weekend

To access the Postgres server, open a terminal and run postgres. You should see the following code:

LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

now, open another terminal window, and then run $ psql. You should have a prompt that looks something like this:

Guest=#

The epicodus at the beginning of the prompt is the name of the default database we created with our username. To keep things easy to read, I'll shorten the prompt to just: #.

Let's create a real database of our own. In psql, type:

CREATE DATABASE test_database;

This is our first SQL statement. SQL keywords are case insensitive but are conventionally typed in all caps. All SQL statements must end with a semicolon. If you press enter and nothing happens, check to see if the prompt has changed from =# to -# - if so, you forgot to put a semicolon at the end of your statement. Just type one and press enter, and your statement should run.

Let's list out all of the databases in our Postgres server with the following command (a forward-slash followed by a lowercase "L"):

\l

This is a command to psql, the program that is interacting with our Postgres server, and not a SQL statement. psql commands start with a backslash and don't end with a semicolon.

There are a couple databases used as templates for other databases, template0 and template1. You shouldn't ever delete those. Postgres also makes a default database called postgres, and if you're on a Mac and created a database with your username, that one will be there, too. And now you should have a database called test_database.

To create tables and columns in our database, we need to connect to it:

\c test_database

Now our prompt should shows that we're connected to thetest_database:

test_database=#

To see a list of psql commands, we can type # \?. We can press the down arrow to scroll through them, or q to quit before the end.

Now, let's create a table in our database:

CREATE TABLE contacts (name varchar, age int, birthday timestamp);

This creates a table called contacts with a column for name, age, and birthday. Just like objects have a class, SQL columns have a data type. In this case, name has the data type varchar, which is means a varying number of characters (this is more or less the SQL equivalent of a string); age is an int, or integer; and birthday is a timestamp, which includes a date and time.

SQL has many data types, but here are the most common ones you'll use: int, float, varchar, text (for long blocks of text), timestamp, and boolean For a full list, see the PostgreSQL data type documentation.

To list the columns in our new table, type # \d contacts.

To see a list of all the tables in your database, type # \dt.

If you mess something up and need to delete a table, type # DROP TABLE table_name;, where table_name is the name of the table.

Here's how to add a column to a table:

ALTER TABLE contacts ADD family boolean;

Drop a column like this:

ALTER TABLE contacts DROP family;

It's a good idea to always have a unique, automatically-incrementing ID number for each record in your database. Here's how to add such a field:

ALTER TABLE contacts ADD id serial PRIMARY KEY;

The serial data type is an autoincrementing integer.

Now that we have tables with columns, let's put some data in them:

INSERT INTO contacts (name, age, birthday) VALUES ('Wes', 43, '1969-05-01');

Notice how we need to put quotes around varchars and timestamps, but not around ints. Also, remember that with SQL, we need to use single quotes - double quotes won't work.

Often, we'll need to get back the ID from an insert, so that we know how to find the row we just inserted. Here's how to return the ID from an INSERT statement:

INSERT INTO contacts (name, age, birthday) VALUES ('Wes', 43, '1969-05-01') RETURNING id;

Now that you have data in your database, let's get it out. Reading data from a database is called querying. Here's a query for you to try:

SELECT name FROM contacts;

This gives you a list of all the names in your contacts table.

You can select multiple columns from a table:

SELECT name, birthday FROM contacts;

If you want to select all of the columns from a table, there's a shortcut:

SELECT * FROM contacts;

You can limit your SELECTs to only return rows that match certain criteria:

SELECT * FROM contacts WHERE age >= 18;

This gives us all of the adults in contacts.

A WHERE clauses accepts the following operators: =, !=, >, <, >=, <=, BETWEEN, LIKE, and IN. You can also prepend any operator with NOT. Here are examples of the last couple operators:

SELECT * FROM contacts WHERE age BETWEEN 13 AND 17;
SELECT * FROM contacts WHERE name BETWEEN 'Judith' AND 'Wilma';
SELECT * FROM contacts WHERE name LIKE 'We%';
SELECT * FROM contacts WHERE birthday IN ('1969-01-01', '1999-01-01');
SELECT * FROM contacts WHERE NOT age >= 18;

For the LIKE operator, the % is a wildcard, meaning it can stand for any number of any characters.

What if we need to change data in our database? There are a lot of fancy ways to do this, but let's focus on the simplest and most common: select a record by its primary key, and update its data:

UPDATE contacts SET name = 'Wes Anderson' WHERE id = 1;

Now, let's delete a record:

DELETE FROM contacts WHERE id = 1;

We're done playing around with this database now, so let's drop it. First, we'll need to connect to our original, default database. If you don't remember what it was called, just type \list. For me, it's called epicodus:

  \c epicodus
 DROP DATABASE test_database;

Finally, to quit psql, just run:

\q

Common SQL Commands


CREATE DATABASE database_name; # From the $USER database.
CREATE TABLE table_name (id serial PRIMARY KEY, some_column varchar, another_column int, yet_another_column timestamp);
ALTER TABLE table_name ADD column_name boolean;
ALTER TABLE table_name DROP column_name;
INSERT INTO contacts (name, age, birthday) VALUES ('Wes', 43, '1969-05-01') RETURNING id;
SELECT * FROM table_name WHERE age >= 18;
SELECT * FROM contacts WHERE NOT age >= 18;
UPDATE contacts SET name = 'Wes Anderson' WHERE id = 1;
DELETE FROM contacts WHERE id = 1;
DROP TABLE table_name; # From the database that holds the table.
DROP DATABASE test_database; # From the $USER database.

Common psql Commands


  • List all databases: \l
  • Connect to database: \c database_name
  • List tables in current database: \dt
  • List columns in a table: \d table_name
  • Exit out of help menu: \q
  • See a list of all psql commands: \? (Press the down arrow to scroll through, or q to exit list.)

Common Terminal Commands


  • $ psql to launch psql.
  • $ postgres to launch postgres.
  • CMD + T (or Terminal > Shell > New Tab from menu options) to open a new tab in the Terminal. This allows you to run postgres and psql at the same time in a single terminal window.

Common Column Types


  • int
  • float
  • varchar
  • text (for long strings of text)
  • timestamp
  • boolean

Operators for WHERE clauses


  • =
  • !=
  • >
  • <
  • >=
  • <=
  • BETWEEN
  • LIKE
  • IN