PostGres - Up and Running

Lesson Objectives

1. Alter a table
2. Limit
3. Sorting
4. Aggregation

Alter a table

Use SELECT * FROM actors; to see changes (Don't forget you can use the up arrow to re-run commands)

-- add an test string column
ALTER TABLE actors ADD COLUMN test VARCHAR(20);

-- drop the test column
ALTER TABLE actors DROP COLUMN test;

-- rename a column
ALTER TABLE actors RENAME height TO height_cm;

-- add an id column that increments with each new row
ALTER TABLE actors ADD COLUMN new_id serial PRIMARY KEY;

-- rename a table
ALTER TABLE actors RENAME TO tv_actors;

Use SELECT * FROM tv_actors; to see changes

See columns and their properties

\d+ tv_actors

You can't undo or rollback changes to the database (in production, be sure to have backups!)

-- rename a table back
ALTER TABLE tv_actors RENAME TO actors;

-- change the data type of a column
ALTER TABLE actors ALTER COLUMN height_cm TYPE decimal;

See columns and their properties

\d+ actors

Limit

-- select all rows from actors table, but show only the first column
SELECT * FROM actors LIMIT 1;

-- select all rows from actors table, but show only one row.  Skip the first row
SELECT * FROM actors LIMIT 1 OFFSET 1;

Sorting

Do not rely on the order you put things into the database. If you need a specific order, you need to explicitly code it

-- select all rows from actors table, order by name alphabetically
SELECT * FROM actors ORDER BY first_name ASC;

-- select all rows from actors table, order by name reverse alphabetically
SELECT * FROM actors ORDER BY first_name DESC;

-- select all rows from actors table, order by height_cm ascending
SELECT * FROM actors ORDER BY height_cm ASC;

-- select all rows from actors table, order by height_cm descending
SELECT * FROM actors ORDER BY height_cm DESC;

Combination

You start to combine what we've learned and start to build more complex queries

SELECT first_name, last_name FROM actors ORDER BY first_name ASC LIMIT 5 OFFSET 2;

Aggregation

 -- divide all rows into groups by name.  Show the SUM of the height of each group.  Also show the name of each group
SELECT SUM(height_cm), first_name FROM actors  WHERE height_cm IS NOT NULL GROUP BY first_name;

-- divide all rows into groups by whether or not they dance.  Show the AVG of the height of each group.  Also show the dance property of each group
SELECT AVG(height_cm), dances FROM actors GROUP BY dances;

-- divide all rows into groups by whether or not the actors sing.  Show the MAX of the height of each group.  Also show the dance property of each group
SELECT MIN(height_cm), sings FROM actors GROUP BY sings;

-- divide all rows into groups by name.  Show the MIN of the height of each group.  Also show the name of each group
SELECT MAX(height_cm), dances FROM actors GROUP BY dances;

-- divide all rows into groups by height.  Show how many rows have a value in the height_cm column.  Also show the name of each group
SELECT COUNT(height_cm), height_cm FROM actors GROUP BY height_cm ORDER BY height_cm ASC;

-- divide all rows into groups by name.  Show the number of rows in each group.  Also show the name of each group
SELECT COUNT(*), first_name FROM actors GROUP BY first_name;

 -- divide all rows into groups by height_cm.  List the names in each group and show the height_cm of each group
SELECT array_agg(first_name), height_cm FROM actors GROUP BY height_cm;