PostgreSQL: Up and Running, 2nd Edition

Chapter 6. Tables, Constraints, and Indexes

Tables form the building block of relational-database storage. Structuring tables so that they form meaningful relationships is the key to relational-database design. In PostgreSQL, constraints enforce relationships between tables. To distinguish a table from just a heap of data, we establish indexes. Much like the indexes you find at the end of books or the tenant list at the entrances to grand office buildings, indexes point to locations in the table so you don’t have to scour the table from top to bottom every time you’re looking for something.

In this chapter, we introduce syntax for creating tables and adding rows. We then move on to constraints to ensure that your data doesn’t get out of line. Finally, we show you how to add indexes to your tables to expedite search. Indexing a table is as much a programming task as it is an experimental endeavor. A misappropriated index is worse than useless. Not all indexes are created equal. Algorithmists have devised different kinds of indexes for different data types, all in the attempt to scrape that last zest of speed from a query.

Tables

In addition to ordinary data tables, PostgreSQL offers several kinds of tables that are rather uncommon: temporary, unlogged, inherited, typed, and foreign (covered in Chapter 10).

Basic Table Creation

Example 6-1 shows the table creation syntax, which is similar to what you’ll find in all SQL databases.

Example 6-1. Basic table creation

CREATE TABLE logs (
	log_id ...