PostgreSQL: Can you create an index in a CREATE TABLE definition?

Question

I want to add indexes to some columns in the create table. Is there a way to add them to the CREATE TABLE definition, or do I need to add them after that with another query?

CREATE INDEX reply_user_id ON reply USING btree (user_id); 

Answer 1

There seems to be no way to specify an index in the CREATE TABLE syntax. However, PostgreSQL creates an index for unique constraints and default primary keys, as described in this note :

PostgreSQL automatically creates an index for each unique constraint and primary key constraint to ensure uniqueness.

In addition, if you want a non-unique identifier, you will need to create it yourself in a separate CREATE INDEX request.

Answer 2

No.

However, you can create unique indexes at creation, but that is because they are classified as constraints. You cannot create a "general" index.