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 foget you can use the up arrow to re-run commands)

-- add an test string columnALTER TABLE actors ADD COLUMN test VARCHAR(20);
-- drop the test columnALTER TABLE actors DROP COLUMN test;
-- rename a columnALTER TABLE actors RENAME height TO height_cm;
-- add an id column that increments with each new rowALTER TABLE actors ADD COLUMN new_id serial PRIMARY KEY;
-- rename a tableALTER 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 backALTER TABLE tv_actors RENAME TO actors;

-- change the data type of a columnALTER 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 columnSELECT * FROM actors LIMIT 1;
-- select all rows from actors table, but show only one row.  Skip the first rowSELECT * 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 alphabeticallySELECT * FROM actors ORDER BY first_name ASC;
-- select all rows from actors table, order by name reverse alphabeticallySELECT * FROM actors ORDER BY first_name DESC;
-- select all rows from actors table, order by height_cm ascendingSELECT * FROM actors ORDER BY height_cm ASC;
-- select all rows from actors table, order by height_cm descendingSELECT * 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 groupSELECT 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 groupSELECT 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 groupSELECT 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 groupSELECT 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 groupSELECT 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 groupSELECT 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 groupSELECT array_agg(first_name), height_cm FROM actors GROUP BY height_cm;