Lesson Weekend

In this lesson, we'll cover a few SQL basics. Note that nothing in this lesson is specific to Ruby. A Postgres database can be used with many languages and SQL commands are commonly used with other relational databases such as MySQL and SQLite.

To access the Postgres server, open a terminal and run postgres. We'll see the following code if Postgres is correctly installed:

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

Open another terminal window and run $ psql. A prompt like this will appear:

Guest=#

This is the name of the default database we created with our username. To keep things easy to read, we'll shorten the prompt to # in the lessons. psql is an interactive terminal that allows us to work with Postgres.

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.

When we create a database in this way, it is being saved on the hard drive of our computer. As long as Postgres is running, we have an open local server which we can use to access this database. If we were to deploy our application (so it is live online), we'd probably host our database elsewhere and we'd use an external server so our application can access the database as needed. While we can technically set up a server and database on our local machines for live deployment, you won't see it happen very often.

Let's list all of the databases in our Postgres server:

\l

This is a psql command, not a SQL statement. psql commands start with a backslash and don't end with a semicolon.

There will likely be a few databases used as templates for other databases: template0 and template1. Don't delete these. Postgres also makes a default database called postgres. If you created a database with your username, that one will be there, too. There should also be our newly created test_database.

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

\c test_database

Now our prompt should show 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.

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 a varying number of characters. It's approximately the SQL equivalent of a string. age is an int, or integer. birthday is a timestamp, which includes a date and time.

SQL has many data types, but here are the most common ones we'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 tables in our database, type \dt.

If we 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 a database. Here's how to add a primary key:

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 add some data:

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, 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 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 we have data in your database, let's retrieve it. Reading data from a database is called querying. Here's an example:

SELECT name FROM contacts;

This returns a list of all the names in the contacts table.

We can select multiple columns from a table:

SELECT name, birthday FROM contacts;

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

SELECT * FROM contacts;

We 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 ways to do this, but let's focus on the simplest and most common. We'll 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 experimenting with our database so let's drop it. First, we'll need to connect to our original, default database. If you don't remember what it's called,type \list. On Epicodus computers, 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

Lesson 3 of 29
Last updated August 7, 2022