PostgresqlHow-ToBeginner · 3 min read

How to Create Hash Index in PostgreSQL: Syntax and Example

In PostgreSQL, you create a hash index using the CREATE INDEX statement with USING HASH. For example, CREATE INDEX index_name ON table_name USING HASH (column_name); creates a hash index on the specified column.

📐

Syntax

The basic syntax to create a hash index in PostgreSQL is:

CREATE INDEX index_name: Names the index.
ON table_name: Specifies the table to index.
USING HASH: Defines the index type as hash.
(column_name): The column to index.

sql

CREATE INDEX index_name ON table_name USING HASH (column_name);

💻

Example

This example creates a hash index on the username column of the users table. It helps speed up equality searches on that column.

sql

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username TEXT NOT NULL,
  email TEXT NOT NULL
);

CREATE INDEX users_username_hash_idx ON users USING HASH (username);

Output

CREATE TABLE CREATE INDEX

⚠️

Common Pitfalls

Hash indexes in PostgreSQL have some limitations:

They only support equality comparisons (=), not range queries.
Before PostgreSQL 10, hash indexes were not WAL-logged, so they could be lost after a crash (now fixed).
They are less commonly used than B-tree indexes, so consider if a B-tree index fits your needs better.

Wrong example (using hash index for range query):

SELECT * FROM users WHERE username > 'a'; -- This will not use the hash index

Right approach: Use B-tree index for range queries.

📊

Quick Reference

Clause	Description
CREATE INDEX index_name	Defines the name of the index to create
ON table_name	Specifies the table to add the index on
USING HASH	Sets the index type to hash
(column_name)	The column to be indexed

✅

Key Takeaways

Use CREATE INDEX index_name ON table_name USING HASH (column_name); to create a hash index.

Hash indexes support only equality comparisons, not range queries.

Since PostgreSQL 10, hash indexes are crash-safe and WAL-logged.

Consider B-tree indexes if you need range queries or more general use.

Hash indexes can speed up lookups on columns with many equality searches.