PostgreSQL, Second Edition

Adding Indexes to a Table

Most of the tables that you have created so far have no indexes. An index serves two purposes. First, an index can be used to guarantee uniqueness. Second, an index provides quick access to data (in certain circumstances).

Here is the definition of the customers table that you created in Chapter 1:

CREATE TABLE customers (
        customer_id   INTEGER UNIQUE,
        customer_name VARCHAR(50),
        phone         CHAR(8),
        birth_date    DATE,
        balance       DECIMAL(7,2)
);

When you create this table, PostgreSQL will display a rather terse message:

NOTICE:  CREATE TABLE / UNIQUE will create implicit 
    index 'customers_customer_id_key' for table 'customers'

What PostgreSQL is trying to tell you here is that even though you didn't explicitly ask for one, an index ...