Thread: PRIMARY KEY and indexes
Hi I come from a MS-SQL background and I'm trying to get some clarity on indexes in Postgres. If anyone can help with the following questions it would be much appreciated: 1. When creating a primary key contraint on a table, is an equivalent index automatically created for you, or would I have to create an index manually? 2. How do I create a clustered index in postgres? Thanks Craig
----- Original Message ----- From: "Craig Bryden" <postgresql@bryden.co.za> To: "pgsql" <pgsql-general@postgresql.org> Sent: Saturday, April 23, 2005 5:45 AM Subject: [GENERAL] PRIMARY KEY and indexes > Hi > > I come from a MS-SQL background and I'm trying to get some clarity on > indexes in Postgres. If anyone can help with the following questions it > would be much appreciated: > > 1. When creating a primary key contraint on a table, is an equivalent > index > automatically created for you, or would I have to create an index > manually? I think if you try it: CREATE TABLE pk_idx_test ( id INT PRIMARY KEY ); You will get: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pk_idx_test_pkey" for table "pk_idx_test" Query returned successfully with no result in 266 ms. > 2. How do I create a clustered index in postgres? See the manual here: http://www.postgresql.org/docs/8.0/interactive/index.html and specifically here: http://www.postgresql.org/docs/8.0/interactive/sql-cluster.html http://www.postgresql.org/docs/8.0/interactive/sql-altertable.html
On Sat, Apr 23, 2005 at 11:45:43AM +0200, Craig Bryden wrote: > 2. How do I create a clustered index in postgres? There is no such thing as a clustered index in Postgres. You can order the table itself following an index (that's what CLUSTER does), but you don't get the data itself inside the index, which is what AFAIK a clustered index in other database systems. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "¿Cómo puedes confiar en algo que pagas y que no ves, y no confiar en algo que te dan y te lo muestran?" (Germán Poo)
Alvaro Herrera wrote: > On Sat, Apr 23, 2005 at 11:45:43AM +0200, Craig Bryden wrote: > > >>2. How do I create a clustered index in postgres? > > > There is no such thing as a clustered index in Postgres. You can order > the table itself following an index (that's what CLUSTER does), but you > don't get the data itself inside the index, which is what AFAIK a > clustered index in other database systems. I believe this is being worked on for 8.1 yes? Sincerely, Joshua D. Drake Command Prompt, Inc. >
On Sat, Apr 23, 2005 at 08:45:25AM -0700, Joshua D. Drake wrote: > Alvaro Herrera wrote: > >On Sat, Apr 23, 2005 at 11:45:43AM +0200, Craig Bryden wrote: > > > > > >>2. How do I create a clustered index in postgres? > > > > > >There is no such thing as a clustered index in Postgres. You can order > >the table itself following an index (that's what CLUSTER does), but you > >don't get the data itself inside the index, which is what AFAIK a > >clustered index in other database systems. > > I believe this is being worked on for 8.1 yes? I haven't heard of such a thing ... Maybe the bizgres people wants to do that. But it's hard to do so I wouldn't expect a usable implementation for 8.1 if they are just starting. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "I personally became interested in Linux while I was dating an English major who wouldn't know an operating system if it walked up and bit him." (Val Henson)