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

postgresql database-schema database-indexes
Xeoncross picture Xeoncross · Jun 4, 2011 · Viewed 40.3k times · Source

I want to add indexes to some of the columns in a table on creation. Is there are way to add them to the CREATE TABLE definition or do I have to add them afterward with another query?

CREATE INDEX reply_user_id ON reply USING btree (user_id);

Answer

rid picture rid · Jun 5, 2011

There doesn't seem to be any way of specifying an index in the CREATE TABLE syntax. PostgreSQL does however create an index for unique constraints and primary keys by default, as described in this note:

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

Other than that, if you want a non-unique index, you will need to create it yourself in a separate CREATE INDEX query.

Related questions

Is it possible to specify the schema when connecting to postgres with JDBC?

Is it possible? Can i specify it on the connection URL? How to do that?

Read More
How to duplicate schemas in PostgreSQL

I have a database with schema public and schema_A. I need to create a new schema schema_b with the same structure than schema_a. I found the function below, the problem is that it does not copy the …

Read More
Cannot create a new table after "DROP SCHEMA public"

Since I wanted to drop some tables and somebody suggested the below and I did it: postgres=# drop schema public cascade; DROP SCHEMA postgres=# create schema public; CREATE SCHEMA Then I got problem when creating a new database, such as: …

Read More