Postgres CREATE INDEX examples

How to create an index on one (or many) columns in Postgres.

Creating a simple compound index of two columns with a unique constraint. In this example the UNIQUE means that no two people can have the same first_name/last_name combination. Be careful with UNIQUE!

content_copyCopy

ALTER TABLE
  "users" CREATE UNIQUE INDEX "fk_example_1" on "public"."users"("first_name" ASC, "last_name" ASC)

A simple index of record creation data. The descending order is useful for a lot of applications where you want to show the most recent items (eg blog posts, comments)

content_copyCopy

ALTER TABLE
  "users" CREATE INDEX on "public"."users"("created_at" DESC)

Using Beekeeper Studio?

Beekeeper Studio is a free and open source database manager for Windows, Mac, and Linux.

This feature is baked right in, so there's no need to manually type SQL every time. Access index editing by right-clicking on a table in the sidebar and clicking 'View Structure', then click 'Indexes' at the top.

Download Beekeeper Studio