Обсуждение: 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.
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
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 ------------------------------------------------------------------