Key Vs Index

Abdul Rahman wrote:

In Oracle, the index is automatically created during the creation of
Primary Key.

The same is true in PostgreSQL. For example, here's a message from a
recent job I ran that created a temp table with SELECT ... INTO and
added a primary key to it:

psql:import_checks.sql:79: NOTICE: ALTER TABLE / ADD PRIMARY KEY will
create implicit index "check_weeks_pkey" for table "check_weeks"

Can you give an example of what you are talking about?

But in PostgreSQL either index is implicitly created

Is "implicitly" in some way intended to mean something distinct to
"automatically"?

I don't find any index against Primary
Key and have to create index on this key.

AFAIK you CAN NOT have a PRIMARY KEY in PostgreSQL without an associated
unique index.

--
Craig Ringer

On Wed, Feb 11, 2009 at 12:09 AM, Abdul Rahman <abr_ora@yahoo.com> wrote:

In Oracle, the index is automatically created during the creation of Primary
Key. But in PostgreSQL either index is implicitly created of the user hast
create it explicitly. I don't find any index against Primary Key and have to
create index on this key.

Let's look:
smarlowe=# create table test (id int primary key, info text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE
smarlowe=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
info | text |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)

See where it says btree(id) under Indexes:? That's telling you it's
got an index.

Note that the index on the FK side isn't auto created.

I have found the answer. PostgreSQL creates index on primary key implicitly and can be seen via \d tablename; command on psql prompt. But PG_Admin-III does not show this index. Sorry to say that I faced several problems because of PG_Admin-III. And I advise you to use psql prompt instead of GUI.

________________________________
From: Abdul Rahman <abr_ora@yahoo.com>
To: pgsql-general@postgresql.org
Sent: Wednesday, February 11, 2009 12:09:25 PM
Subject: Key Vs Index

In Oracle, the index is automatically created during the creation of Primary Key. But in PostgreSQL either index is implicitly created of the user hast create it explicitly. I don't find any index against Primary Key and have to create index on this key.

Scott Marlowe wrote:

Note that the index on the FK side isn't auto created.

Of course, you often don't want one - you might rarely or never DELETE
from the referenced table or UPDATE the primary key value. In that case,
the index just slows down updates and deletes on the table with the fk
without gaining you anything.

--
Craig Ringer

On Wed, Feb 11, 2009 at 12:37 AM, Abdul Rahman <abr_ora@yahoo.com> wrote:

I have found the answer. PostgreSQL creates index on primary key implicitly
and can be seen via \d tablename; command on psql prompt. But PG_Admin-III
does not show this index. Sorry to say that I faced several problems because
of PG_Admin-III. And I advise you to use psql prompt instead of GUI.

Yeah, I'm a big big fan of psql. Try tab completion on for size,
that's really cool, but I wish it worked for more situations. be sure
and look through all the \ commands, there's a ton of them, and some
are quite useful, \i for input a file, \o for output stdout to a
file, and so on...

I have to say I'm very spoiled by psql, and would have killed for an
equivalent on oracle back when I had to keep it happy. Closest
compromise I ever got was using rlwrap on it's sql command like tool.

On Wed, Feb 11, 2009 at 7:37 AM, Abdul Rahman <abr_ora@yahoo.com> wrote:

I have found the answer. PostgreSQL creates index on primary key implicitly
and can be seen via \d tablename; command on psql prompt. But PG_Admin-III
does not show this index.

pgAdmin shows the primary key which is the index.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

On 11/02/2009 07:37, Abdul Rahman wrote:

But PG_Admin-III does not show this index. Sorry to say that I faced
several problems because of PG_Admin-III. And I advise you to use
psql prompt instead of GUI.

I think that's a little unfair. PgAdmin is IMHO a great tool, and I've
found it invaluable; and I use psql all the time too.

Would you care to expand on the problems you've run into? - either here
or on the pgadmin-support list.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------