Beginning Databases with PostgreSQL

Beginning Databases with PostgreSQL

Although this book is concerned with PostgreSQL version 8 and its database design is questionable, it's still a pretty good source for learning Postgres and SQL in general.

Notes:

1. Introduction to PostgreSQL

2. Relational Database Principles

3. Getting Started with PostgreSQL

• pg. 55 - Creating Users (using the createuser.exe)
• pg. 66 - Creating Databases (using the createdb.exe)
• pg. 67 - Creating Tables
• pg. 68 - Deleting Tables When a table is dropped, its sequence relations are also deleted so there's no need to delete them manually.
• pg. 69 - Populating Tables

4. Accessing Your Data

This entire chapter is devoted to the SELECT statement.

• pg. 74 - Connecting to PostgreSQL using psql.exe
• pg. 75 - Connecting to a remote server.
• pg. 78 - Essential psql commands list
• pg. 87 - Standard comparison operators
• pg. 94 - PostgreSQL Date/Time formatting (ISO-8601, YYYY-MM-DD hh:mm:ss.ssTZD)
• pg. 99 - Extracting parts of a date (year, month, day, hour, minute, second)
• pg. 103 - Relating two tables
• pg. 105-106 - Aliasing table names
• pg. 106 - Relating three or more tables.
• pg. 111 - SQL92 JOIN syntax

5. PostgreSQL Command-Line and Graphical Tools

• pg. 118 - psql Command-Line Quick Reference
• pg. 119 - psql Internal Commands Quick Reference

6. Data Interfacing

• pg. 153 - INSERTing data
• pg. 155 - Caution Avoid providing values for serial data columns when inserting data
• pg. 155 - Manipulating the serial value
• pg. 160 - Copying data from flat files (CSV or files using other delimiters)
• pg. 166 - Updating data (the UPDATE statement)
• pg. 169 - The DELETE statement
• pg. 170 - TRUNCATE (deletes all the rows from the specified table)

7. Advanced Data Selection

• pg. 174 - Aggregate functions list
• pg. 176 - GROUP BY clause
• pg. 178 - HAVING clause
• pg. 185 - Subqueries

8. Data Definition and Manipulation

• pg. 201 - Data Types in PostgreSQL
• pg. 215 - Magic Variables (CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP...)
• pg. 217 - CREATE TABLE in more depth
• pg. 218 - Column constraints
• pg. 222 - Table constraints
• pg. 223 - ALTERing the table structure
• pg. 228 - VIEWs
• pg. 232 - Foreign Key Constraints
• pg. 234 - Foreign Key (table-level) constraint syntax
• pg. 241 - Foreign Key ON UPDATE & ON DELETE options

9. Transactions and Locking

• pg. 246 - ACID Rules
• pg. 256 - Dirty Reads (PostgreSQL doesn't allow them)
• pg. 257 - Unrepeatable Reads (permitted by default in PostgreSQL but it can be prohibited)
• pg. 258 - Phantom Reads (allowed by default in PostgreSQL)
• pg. 258 - Lost Updates and a simple solution for them
• pg. 261 - Changing the Isolation Level

Tips:

Keep transactions small to avoid excessive locking (and deadlocks) and performance issues. Transactions should not be kept open over extended time periods.

10. Functions, Stored Procedures and Triggers

• pg. 2

---

Reading plan:

Tuesday - Chapters 10, 11 (90 pages)

Wednesday - Chapter 12 and Appendixes A to F (83 pages)