Thread: Are Indices automatically generated for primary keys?
Hi,
Are indices for columns marked with "PRIMARY KEY" automatically generated by postgresql, or do I have to do it manually?
The question might seem dumb, I ask because I remember from working with MySQL it generates indices automatically in this case.
Thank you in advance, Clemens
Are indices for columns marked with "PRIMARY KEY" automatically generated by postgresql, or do I have to do it manually?
The question might seem dumb, I ask because I remember from working with MySQL it generates indices automatically in this case.
Thank you in advance, Clemens
On Wed, Aug 18, 2010 at 03:51:22PM +0200, Clemens Eisserer wrote: > Hi, > > Are indices for columns marked with "PRIMARY KEY" automatically generated by > postgresql, or do I have to do it manually? > The question might seem dumb, I ask because I remember from working with > MySQL it generates indices automatically in this case. they are generated automatically. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
Clemens Eisserer <linuxhippy@gmail.com> wrote: > Are indices for columns marked with "PRIMARY KEY" automatically > generated by postgresql, or do I have to do it manually? If you look at the documentation page for CREATE TABLE, you'll see the following: | PostgreSQL automatically creates an index for each unique | constraint and primary key constraint to enforce uniqueness. Thus, | it is not necessary to create an index explicitly for primary key | columns. (See CREATE INDEX for more information.) http://www.postgresql.org/docs/current/interactive/sql-createtable.html There's a lot of information on the page, but if you use your browser to search for PRIMARY KEY within the page, it's not too hard to find. Also, if you create a primary key or a unique constraint on a table, you should see a notice informing you of the creation of the index, and its name. -Kevin
Hi, > they are generated automatically. Thanks depesz! The reason why I asked was because pgAdmin doesn't display the automatically created indices, which confused me. Thanks, Clemens PS: > If you look at the documentation page for CREATE TABLE, you'll see > the following ..... but if you use your > browser to search for PRIMARY KEY within the page, it's not too hard > to find. Its quite harsh to imply I didn't look for documentation. I looked at the "Indexes and ORDER BY" which doesn't mention it, or I've overlook it. Doesn't make a difference anyway. > Also, if you create a primary key or a unique constraint on a table, > you should see a notice informing you of the creation of the index, > and its name. I use Hibernate, and it generates the DDL for me. Even with debug/DDL/SQL-output enabled, I don't get any hint that an index was created.
Clemens Eisserer <linuxhippy@gmail.com> wrote: > Its quite harsh to imply I didn't look for documentation. Sorry; I didn't mean to be harsh. PostgreSQL has excellent documentation, and we strive to make it better all the time. Sometimes people coming from some other products aren't used to that -- I was just trying to point you in the direction of being able to find things in the future, to save you trouble and delay. > I looked at the "Indexes and ORDER BY" which doesn't mention it, > or I've overlook it. > Doesn't make a difference anyway. Well, it very much does make a difference, because when someone makes the effort to find something in our documentation, and in spite of their best efforts they can't, we tend to consider that a bug in the documentation. I'll take a look at the page you mentioned and see if I can work in a suitable reference. I'm sure you can see, though, why the *main* place it was documented was the statement which is generally used to create a primary key. Thanks for responding with the info on where you looked. -Kevin
Clemens Eisserer <linuxhippy@gmail.com> wrote: > I looked at the "Indexes and ORDER BY" which doesn't mention it The doesn't seem like an appropriate place to discuss when indexes are created. Do you think you'd have found a mention in the Introduction page for indexes? Since we talk about the CREATE INDEX statement there, it seems reasonable to me to add a mention of where they are automatically created by constraints, too. Did you try the documentation index? If so, where did you look? -Kevin
On 8/18/2010 9:15 AM, Clemens Eisserer wrote: > Hi, > > >> they are generated automatically. >> > Thanks depesz! > The reason why I asked was because pgAdmin doesn't display the > automatically created indices, which confused me. > > Thanks, Clemens > PGAdmin caches all database layout locally, the tree view can get very stale. So refresh the treeview with either F5 or right click an item in the treeview click refresh to rebuild the list. **snip*** All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by ourproprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other informationproprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses.If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mailimmediately. Thank you.
On 18 August 2010 17:06, Justin Graf <justin@magwerks.com> wrote: > On 8/18/2010 9:15 AM, Clemens Eisserer wrote: >> Hi, >> >> >>> they are generated automatically. >>> >> Thanks depesz! >> The reason why I asked was because pgAdmin doesn't display the >> automatically created indices, which confused me. >> >> Thanks, Clemens >> > PGAdmin caches all database layout locally, the tree view can get very > stale. So refresh the treeview with either F5 or right click an item in > the treeview click refresh to rebuild the list. > I don't think PgAdmin will display indexes created by primary keys, only if indisprimary is false. -- Thom Brown Registered Linux user: #516935
Le 18/08/2010 17:23, Thom Brown a écrit : > On 18 August 2010 17:06, Justin Graf <justin@magwerks.com> wrote: >> On 8/18/2010 9:15 AM, Clemens Eisserer wrote: >>> Hi, >>> >>> >>>> they are generated automatically. >>>> >>> Thanks depesz! >>> The reason why I asked was because pgAdmin doesn't display the >>> automatically created indices, which confused me. >>> >>> Thanks, Clemens >>> >> PGAdmin caches all database layout locally, the tree view can get very >> stale. So refresh the treeview with either F5 or right click an item in >> the treeview click refresh to rebuild the list. >> > > I don't think PgAdmin will display indexes created by primary keys, > only if indisprimary is false. > pgAdmin doesn't display indexes for primary keys and unique constraints. These objects are already displayed in the constraints nodes. The fact that they use an index to enforce the constraints is an implementation detail. -- Guillaume http://www.postgresql.fr http://dalibo.com