PRIMARY KEY and indexes

----- 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)