PostGres - Up and Running

Lesson Agenda

1. What is Posgresql
2. Instantiate a new DB directory
3. Connect to PostGres through CLI
4. Create a Database
5. Create a table
6. Insert into the table
7. Select from table
8. Update the table
9. Delete from table

What is Postgres

One of the most common type of databases are relational (think columns and rows in contrast to MongoDB's documents that were very similar to JSON).

Unlike an excel or google spreadsheet (where many of us have typically seen data stored in tables and rows), relational databases are far bigger, more powerful and allow for relations across many tables. We'll talk about table relationships in the afternoon.

SQL is short for Structured Query Language. There are a few popular relational databases like mySQL, Oracle, SQL Server, SQLite and Postgresql.

SQL is the language used to query and interact with these databases. Each database is a little different but much of the fundamentals of SQL apply to each of the above databases.

Postgres is an open source RDBMS (relational database management system) created at University of California, Berkeley. It started being built in 1982.

Extra

It is ACID-compliant and transactional

Instantiate a new DB directory

If using the OSX app that https://www.postgresql.org/ provides, you can tell it where to store its data:

initdb -D ~/Desktop/postgres_db/ - create the directory
postgres -D ~/Desktop/postgres_db/ - start postgres, using that directory

If you used homebrew to install, use

postgres -D /usr/local/var/postgres/

Connect to PostGres through CLI

If using the OSX app that https://www.postgresql.org/ provides, you can list sub dbs from the terminal:

psql -l -- list all subdatabses, if you found one that you want to use run the following:

psql db_name -- start psql app, using the sub database db_name

-- if you need to create a db to use
createdb intro_to_sql
psql intro_to_sql

Once inside the psql app, you can list the sub databases like this:

\l

SQL's Big Gotcha

SQL statements require being ended with a ;

Most of your postgres prompts will look like this

If you forget your semi-colon, the prompt will drop to the next line and change slightly

You must add a semi-colon to end your statement

SQL Syntax

Even though keywords in SQL are not case sensitive, the convention is to capitalize them.

-- correct
SELECT * FROM actors;

-- incorrect
select * from actors;

Notice, comments can be new line or after a line and they start with two dashes --

Create a Database

Like MongoDB, Postgres has "sub-databases":

-- create the sub database foo
CREATE DATABASE foo;

-- drop it
DROP DATABASE foo;

-- get started with our code along
CREATE DATABASE test_db;

-- connect to the test_db sub database
\connect test_db;

-- OR (does the same thing as connect, just shorthand)
\c test_db;

Data types

Postgres has the following data types (most common):

1. int - whole number
2. decimal - float/decimal
3. bool - boolean
4. varchar(n) - small text
5. text - large text
6. timestamp - date

Extra II

Hello, I'm Mr. Null. My Name Makes Me Invisible to Computers

Create a table

• Instead of collections, we have tables, which are just like a spreadsheet, or grid. Rows are entries, and columns are properties of each row.
• Unlike MongoDB, you have to tell Postgres, you have to specify each column and what is the data type for each column. It's very 'strict'

-- describe your tables
CREATE TABLE foo ( name varchar(20) ); -- create a table called 'foo' with one column called 'name' which is a small text column

-- see table
\dt

-- drop a table
DROP TABLE foo;

-- 'actors' table has an id column, which is just a number that increases with each addition, and columns for first_name, last_name, height (in mm), and boolean properties for sing and dance

CREATE TABLE
  actors
  ( id serial, first_name varchar(20) NOT NULL, last_name varchar(20), height int, sings BOOLEAN, dances BOOLEAN DEFAULT false);

-- describe the columns of the test sub database
\d actors;

Insert into the table

You don't have to remember the order of the columns that you created, but you do have to match the order in the insert

INSERT INTO
  actors ( height, first_name, sings, last_name, dances )
VALUES
  ( 179 , 'Caity' , false, 'Lotz', true ); -- create a row

You also don't have to enter all the fields (only the ones required)

INSERT INTO actors (first_name) VALUES ('Sting');

Let's copy paste a few more actors so we can play around with SQL some more

INSERT INTO actors (first_name, last_name, height, sings, dances) VALUES
('Melissa', 'Benoist', 173, true, false),
('Nicole', 'Maines', 170, true, true),
('Brandon', 'Routh', 189, false, false),
('Amy Louise', 'Pemberton', 160, null, null),
('Dominic', 'Purcell', null, null, null),
('Nick', 'Zano', 183, null, null),
('Maisie', 'Richardson-Sellers', null, null, null),
('Franz', 'Drameh', 180, null, null),
('Victor', 'Garbor', null, true, null),
('Tala', 'Ashe', 168, null, null),
('Arthur', 'Darvill', null, null, null),
('Jess', 'Macallan', 175, false, true),
('Matt', 'Ryan', 180, true, true),
('Adam', 'Tsekhman', null, null, null),
('Courtney', 'Ford', 165, null, null),
('Neil', 'McDonough', null, true, true),
('Ramona', 'Young', null, null, null),
('Melissa', 'McCarthy',157, true, true),
('Jenny', 'McCarthy',null, false, false);

Select from table

-- select all rows from the actors table.  display only the name column
SELECT first_name FROM actors;

 -- select all rows from the actors table.  display only the all columns
SELECT * FROM actors;

-- select all rows from the actors table where the name column is set to 'Tala'
SELECT * FROM actors WHERE first_name = 'Tala';

-- select all rows from the actors table where the name column is set to 'tala' or 'Tala' or 'TALA' (case insensitive
SELECT * FROM actors WHERE first_name ILIKE 'Tala';

-- select all rows from the actors table where the name column contains 'Mel'
SELECT * FROM actors WHERE first_name LIKE '%Mel%';

-- select all rows from the actors table where the name column is set to 'Melissa' AND the email column is set to McCarthy
SELECT * FROM actors WHERE first_name = 'Melissa' AND last_name = 'McCarthy';

-- select all rows from the actors table where either the first_name column is set to 'Ramonoa' OR the email column is set to last_name is equal to 'Ford'
SELECT * FROM actors WHERE first_name = 'Ramona' OR last_name = 'Ford';

-- select all rows from the actors table where the height column is set to 180
SELECT * FROM actors WHERE height = 180;

-- select all rows from the actors table where the height column is not set to 180
SELECT * FROM actors WHERE IS NOT height = 180;

-- select all rows from the actors table where the height column is greater than 165
SELECT * FROM actors WHERE height > 165;

 -- select all rows from the actors table where the height column is less than 165
SELECT * FROM actors WHERE height < 165;

-- select all rows from the actors table where the height column is greater than or equal to 165
SELECT * FROM actors WHERE height <= 165;

-- select all rows from the actors table where the height column is less than or equal to 165
SELECT * FROM actors WHERE height >= 165;

SELECT * FROM actors WHERE height IS NULL;

-- select all rows from the actors table where the height column has no value
SELECT * FROM actors WHERE height IS NOT NULL; -- select all rows from the actors table where the height column has any value

Update the table

-- update the actors table.  Set the height column to 181 for every row that has the id column set to 2
UPDATE actors SET height = 181 WHERE id = 2;

Delete from table

 -- delete all rows from the actors table that have the id column set to 21
DELETE FROM actors WHERE id = 21;