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.
- 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
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
psqlcommands 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
- pg. 118 -
psqlCommand-Line Quick Reference - pg. 119 -
psqlInternal Commands Quick Reference
- pg. 153 - INSERTing data
- pg. 155 - Caution Avoid providing values for
serialdata columns when inserting data - pg. 155 - Manipulating the
serialvalue - 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)
- pg. 174 - Aggregate functions list
- pg. 176 - GROUP BY clause
- pg. 178 - HAVING clause
- pg. 185 - Subqueries
- 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
- 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
Keep transactions small to avoid excessive locking (and deadlocks) and performance issues. Transactions should not be kept open over extended time periods.
- pg. 271 - PostgreSQL Arithmetic Operators
- pg. 272 - Comparison Operators
- pg. 274 - Common Mathematical Functions
- pg. 279 - Functions = Stored procedures in PostgreSQL
- pg. 282 - Stored Procedures in more depth (dollar quotes are preferred)
- pg. 285 - PL/pgSQL Reserved Keywords
- pg. 295 - Stored Procedure example
- pg. 299 - Triggers
- pg. 303 - Trigger procedure variables
- pg. 306 - Why use stored procedures and triggers
- pg. 314 -
postgresql.confoptions - pg. 322 - CREATE USER Syntax
- pg. 329 - CREATE DATABASE Syntax
- pg. 331 - Schemas
- pg. 337 - Privilege Management
- pg. 338 - Database Backup and Recovery
- pg. 347 - Database Performance
- pg. 348 - VACUUM
- pg. 350 - EXPLAIN
- pg. 354 -
psql's\timingoption
- pg. 376 - Currency data-type choices (TLDR; use numeric(P, S))
- pg. 380 - Common Patterns
I've skipped the following chapters since they deal with specific implementations:
If the need arises to use PostgreSQL along with any of these languages, these chapters can serve as a reference.
- pg. 547 - `money' data type is deprecated, use numeric(9, 2) instead.
- pg. 570 - SELECT Syntax
The psql options to remember are -U for username and -d for database to which to connect to.
Commonly used internal commands are: \c, \cd, \i, \copy, \d{t|i|s|v|S}, \dl, \lo-import, \l, \o and \r
If we want to store images, PDFs etc. directly in the database, the best way is to store them as BLOBs.