Re: primary key index

From: Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: primary key index
Date: 2007-05-14 13:03:03
Message-ID: 46485E07.6090900@cox.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Really?

toll_archive=# create table snaggle (
toll_archive(# foo integer,
toll_archive(# bar integer,
toll_archive(# primary key (foo));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"snaggle_pkey" for table "snaggle"
CREATE TABLE
Time: 240.183 ms
toll_archive=# \d snaggle
Table "public.snaggle"
Column | Type | Modifiers
- --------+---------+-----------
foo | integer | not null
bar | integer |
Indexes:
"snaggle_pkey" PRIMARY KEY, btree (foo)

On 05/14/07 04:32, Sim Zacks wrote:
> It is mixture of looking and knowing where to look. I read the section
> on indexes where it says that PostGreSQL creates a unique index, but I
> didn't see an index on the table so I didn't know if it was implicit or
> if for some reason my primary keys weren't becoming indexes. I didn't
> see the create table documentation which states that it is not necessary
> to explicitly create them.
>
> Thank you for your help.
>
>> PostgreSQL automatically creates a unique index when a unique
>> constraint or a primary key is defined for a table. The index covers
>> the columns that
>> make up the primary key or unique columns (a multicolumn index, if
> appropriate),
>> and is the mechanism that enforces the constraint.
>
>
> Ron Johnson wrote:
> On 05/14/07 03:27, Sim Zacks wrote:
>>>> Is a primary key an implicit index, or if I want to join on that field
>>>> is it also worthwhile to make an explicit index?
>
> On-line documentation is such a wonderful resource...
>
> http://www.postgresql.org/docs/7.3/interactive/sql-createtable.html
>
> PostgreSQL automatically creates an index for each unique
> constraint and primary key constraint to enforce the uniqueness.
> Thus, it is not necessary to create an explicit index for
> primary key columns.
>
> (Yes, I purposefully used the doc set of an ancient version.)
>

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGSF4HS9HxQb37XmcRAm8WAJsGNYwp6zlrQxjPoUsikIlzr9p3/ACbBuwI
5gITI5aqS+vkpsxVZqHrpiE=
=hJp/
-----END PGP SIGNATURE-----

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Alban Hertroys 2007-05-14 13:05:59 Re: Streaming large data into postgres [WORM like applications]
Previous Message John D. Burger 2007-05-14 12:51:14 Re: Streaming large data into postgres [WORM like applications]