Join Bill Weinman for an in-depth discussion in this video Prerequisites, part of PostgreSQL 9 with PHP Essential Training.
- View Offline
PostgreSQL is typically pronounced as post-gres. Some say PostgreSQL, but many consider that unwieldy and simply say post-gres. Like most SQL databases, Postgres does its best to conform to ANSI SQL standard-- in this case, the latest version, SQL 2008-- but will break the standard where necessary for capability or compatibility reasons. In reality, there are no fully compliant SQL database systems, because the SQL standard does not describe a complete database management system.
Additionally, most mature database systems pre-date the standard and maintain some non-compliant syntax in order to preserve compatibility with legacy code. Postgres, dating back to the Ingres project in the early 1980s, with its SQL support beginning in the early 1990s, has a great deal of legacy to support. Postgres has all the features you would expect from a major DBMS, including triggers, views, transactions, save points, foreign keys, full-text search, replication, concurrency, object inheritance, storage procedures, a rich set of data types, multiple indexing systems, and many, many more.
For this course, we will cover the essential features necessary to use this powerful system for simple common projects. Postgres is suitable for many purposes, from a small web site project to a major corporate-distributed database system. It supports very large data stores with excellent performance, comparable to current offerings from major vendors like Oracle or Microsoft. In order to get the most out of this course, you should have some programming experience and some knowledge of SQL.
If you need to learn the basics of SQL, please see my course SQL Essential Training here on the lynda.com Online Training Library. You'll need the have Postgres 9.0 or greater installed on your system. There are movies showing how to install Postgres on Mac, Windows, and Linux in this course. Postgres comes with a database administration program called pgAdmin3. You may use the SQL Query Module in pgAdmin to follow along with all the exercises in this course.
In order to edit source code, you will need a good text editor. A text editor is different from a word processor. A word processor adds extra information to text files that will make them not work for source code. Do not use a word processor. For a text editor, I recommend BBEdit or TextWrangler on a Mac, or Notepad++ on a PC. Do not use the Notepad program that comes with Windows, as it is not sufficient for this purpose. In order to demonstrate the PHP features in this course, I'm using XAMPP, X-A-M-P-P, a quick and simple package installer for Apache and PHP that runs on all modern operating systems.
Along with XAMPP, I'm using a program called SID, which I developed for demonstrating and testing SQL for my SQL-related courses here on lynda.com. You do not need to use SID in order to follow along with the exercises in this course; all the exercises will work fine using pgAdmin3. I find SID easy to use, and I think you will too, but it's not necessary. There are movies demonstrating how to install XAMPP and SID in this chapter.
- Installing PostgreSQL
- Understanding database architecture
- Inserting, updating, and deleting data in a table
- Creating a database library
- Indexing ID fields
- Storing numbers, text, and Boolean values
- Reading data
- Using casts to force type
- Using mathematical functions
- Concatenating strings
- Working with date and time functions and operators
- Defining CRUD
- Using PHP to insert, read, update, and delete rows in a database