Skip to main content

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 directorypostgres -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 usecreatedb intro_to_sqlpsql 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.

-- correctSELECT * FROM actors;
-- incorrectselect * 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 fooCREATE DATABASE foo;
-- drop itDROP DATABASE foo;
-- get started with our code alongCREATE 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#

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 tablesCREATE 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 tableDROP 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 columnSELECT first_name FROM actors;
 -- select all rows from the actors table.  display only the all columnsSELECT * 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 insensitiveSELECT * 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 McCarthySELECT * 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 180SELECT * FROM actors WHERE height = 180;
-- select all rows from the actors table where the height column is not set to 180SELECT * FROM actors WHERE IS NOT height = 180;
-- select all rows from the actors table where the height column is greater than 165SELECT * FROM actors WHERE height > 165;
 -- select all rows from the actors table where the height column is less than 165SELECT * FROM actors WHERE height < 165;
-- select all rows from the actors table where the height column is greater than or equal to 165SELECT * FROM actors WHERE height <= 165;
-- select all rows from the actors table where the height column is less than or equal to 165SELECT * 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 valueSELECT * 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 2UPDATE actors SET height = 181 WHERE id = 2;

Delete from table#

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