Create Table with Index in PostgreSQL Database

In PostgreSQL, indexes are used to improve the performance of data retrieval operations by reducing the time required to locate specific rows in a table. You can create indexes on columns to optimize queries, and you can define them at the time of table creation or later using the CREATE INDEX command.

In this tutorial, we will explain how to create a table with indexes, demonstrates various scenarios, and provides detailed examples.

Basic Syntax

There are two main ways to create an index when creating a table:

  • Unique Constraints: Automatically creates a unique index for the column(s).
  • Explicit Index Creation: Create an index manually using the CREATE INDEX statement after the table is created.

Example Syntax:

  • Using a unique constraint: CREATE TABLE ... UNIQUE(column_name);
  • Explicit index creation: CREATE INDEX index_name ON table_name(column_name);

Example 1: Create Table with Unique Index

Let’s create a table named users where the email column has a unique constraint. This ensures that the email column is unique across all rows, and PostgreSQL automatically creates a unique index for this column.

</>
Copy
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

Explanation: The UNIQUE constraint on the email column automatically creates a unique index to enforce the constraint. This index ensures that no two rows have the same email address.

Insert sample data into the table:

</>
Copy
INSERT INTO users (username, email)
VALUES 
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com');

Attempt to insert a duplicate email:

</>
Copy
INSERT INTO users (username, email)
VALUES ('Charlie', 'alice@example.com');

Result: PostgreSQL will throw an error because the email column must be unique:

Example 2: Explicit Index Creation

Sometimes, you may want to create a non-unique index for faster query performance. Let’s create a table named products, and then manually add an index on the price column.

</>
Copy
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

Create an index on the price column:

</>
Copy
CREATE INDEX idx_price ON products(price);

Insert sample data into the products table:

</>
Copy
INSERT INTO products (product_name, price)
VALUES 
('Product A', 19.99),
('Product B', 29.99),
('Product C', 9.99);

Query the table and filter results based on the price column:

</>
Copy
SELECT * FROM products WHERE price < 20;

Explanation: The manually created index on the price column improves the performance of queries filtering on this column, as PostgreSQL uses the index to speed up the search.

Example 3: Composite Index

You can create a composite index on multiple columns to optimize queries that filter or sort by multiple criteria. Let’s extend the products table and create a composite index on the product_name and price columns.

</>
Copy
CREATE INDEX idx_name_price ON products(product_name, price);

Query the table using both columns:

</>
Copy
SELECT * FROM products
WHERE product_name = 'Product A' AND price = 19.99;

Explanation: The composite index improves the performance of queries that use both product_name and price in their filtering criteria. This is particularly useful for optimizing multi-column searches.

Conclusion

Indexes in PostgreSQL are a powerful tool for improving query performance. You can create them automatically using constraints like UNIQUE, or explicitly for specific optimization needs. By understanding the use of single-column indexes, composite indexes, and unique constraints, you can design your database to handle queries efficiently.