PostgreSQL is the world's most powerful open-source database. Free to download, use, and deploy, and distributed with a liberal BSD/MIT license, PostgreSQL has been under development for more than 15 years by a global team of programmers, and is used by many companies, including Skype, BASF, and Affilias (which administers the .info and .org domains). It is widely acknowledged to offer the greatest number of features of any open-source database, and consistently performs better than other open-source database servers when benchmarking mixed read/write applications with many connections.
This course introduces students to relational databases in general, and to PostgreSQL in particular.
Students will learn the basics of creating a database, using the built-in types that come with PostgreSQL, and the many ways in which they can manipulate this data. They then learn how to ensure the integrity of their data using PostgreSQL's verification mechanisms, including triggers, defaults, and check clauses. Students then learn how to create their own custom data types, and their own functions (in several different languages) that know how to work with such types.
The course concludes with a discussion of query optimization, backups, replication, and server configuration.
Understand PostgreSQL database concepts
Install, configure, and upgrade PostgreSQL servers
Relational database design, including normalization
DDL (data definition language), DML (data manipulation language)
Query design, including advanced queries using various joins
Writing server-side functions
Security in PostgreSQL, and configuration for maximum security and flexibility
Optimizing storage space
Backups and restoration of data
Programmers, database administrators, and technical managers considering the use of PostgreSQL in their work
Programmers who are using PostgreSQL to store data, who want to get the most out of the system
Database administrators familiar with other databases, and switching to PostgreSQL
Introduction to PostgreSQL
Data types (and their functions)
Psql client program
Conditional and cleverness
Importing and exporting data
Full-text indexing and search
Other programming languages
Creating new data types
Geographical data with PostGIS
Backups, recovery, and database management
Prior experience installing software and programming in any language, and particularly a high-level "scripting" language such as Python, Ruby, Perl, or PHP, is very strongly recommended. Familiarity with relational databases is helpful but not required.