Best unofficial Apache Server developers community
Username
Forgot password?
Sign in with Twitter account
Sign in with Facebook account

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

⇧
1
⇩
71 views

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);
postgresql database-schema
asked June 4, 2011 4:58 pm CDT
Xeoncross
posted via StackOverflow

2 Answers

⇧
4
⇩
Best answer
 

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.

answered June 4, 2011 5:24 pm CDT
Radu
⇧
2
⇩
 

No.

However, you can create unique indexes in the create, but that's because they are classed as constraints. You can't create a "general" index.

answered June 4, 2011 5:24 pm CDT
Bohemian

Your answer

Join with account you already have

Sign in with Twitter account
Sign in with Facebook account
Sign in with Google Friend Connect

Preview
Similar questions
Can I have create an InnoDB table without a PK? If so, would a secondary index be used as the clustered index?
March 30, 2011
Can I have create an InnoDB table without a PK? If so, would a secondary index be used as the clustered index?
March 30, 2011
Should I create an Index on my table?
April 18, 2011
How to create index in two table mysql?
January 13, 2011
Can I change the datatype for the "Create Table" field in the Show Create Table function in MySQL?
January 10, 2011
How to create index for table having 30 columns where Select statement is generated dynamically
February 10, 2011
Postgresql - CREATE TABLE SYNTAX ERROR USING LIKE -
February 2, 2011
Create temporary table from select statement without using Create Table
May 2, 2011
Pymongo / MongoDB: create index or ensure index?
May 6, 2011
Should I create an index on these columns?
May 12, 2011
Create fulltext index on a VIEW
March 14, 2011
Create an index only on certain rows in mysql
February 3, 2011
If I create a Postgres index what's the difference between ASC and DESC?
May 25, 2011
Create index to optimize slow query
March 29, 2011
Could not create unique index - claims to have duplicate values but it doesn't
January 18, 2011
Use mod_rewrite to create custom directory index in Apache
February 6, 2011
How to create thumbnails within PostgreSQL
April 28, 2011
How do create a SET like type on postgresql
March 3, 2011
Cannot rake db:create:all -- Couldn't create database for {"encoding"=>"utf8", "username"=>"root", "adapter"=>"mysql" ...
April 6, 2011
Codeigniter: I would like create user URL's for my users when they create a username
January 8, 2011
How to create a file using postgresql functions ?
January 17, 2011
How to create a file using postgresql function call?
January 17, 2011
Rake db:create encoding error with postgresql
April 28, 2011
PostgreSQL: dynamically create result columns
May 2, 2011
Postgresql. CREATE CAST 'character varying' to 'integer'
January 21, 2011
How to create Domain column type in Django with PostgreSQL?
March 24, 2011
When should I create php files to create tables in my sql database?
April 25, 2011
PostgreSQL - create a function that is using data from two columns to update another column
January 16, 2011
What's the required to make a normal user can create schema on PostgreSQL?
February 27, 2011
Create table from select then alter table by adding an auto increment column in mysql
January 11, 2011