Обсуждение: Serial and Index
I read at http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.1 that when a serial is created then an index is created on the column. However I can't seem to find this anywhere in the PoistgreSQL manual. Is this true? Thanks. -Sam
On Sun, Feb 27, 2005 at 12:54:52PM +0000, Sam Adams wrote: > I read at http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.1 That copy of the FAQ is over two years old according to the "Last updated" text at the top of the page. You can find the current FAQ here: http://www.postgresql.org/files/documentation/faqs/FAQ.html > that when a serial is created then an index is created on the column. > However I can't seem to find this anywhere in the PoistgreSQL manual. Is > this true? Thanks. Not since 7.3. Here's an excerpt from the 7.3 Release Notes: * No longer automatically create index for SERIAL column (Tom) You can confirm this for yourself by trying it and observing what happens. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Sun, Feb 27, 2005 at 12:54:52 +0000, Sam Adams <samadams@myfastmail.com> wrote: > I read at http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.1 > that when a serial is created then an index is created on the column. > However I can't seem to find this anywhere in the PoistgreSQL manual. Is > this true? Thanks. That was true with the first release that had the serial pseudo type. That was changed in later versions to provide flexibility for DBAs. In common use, a serial column is declared as the primary key and you get a unique index created without any other special declarations.
On Sun, 27 Feb 2005 12:54:52 +0000, Sam Adams <samadams@myfastmail.com> wrote: > I read at http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.1 > that when a serial is created then an index is created on the column. > However I can't seem to find this anywhere in the PoistgreSQL manual. Is > this true? Thanks. The FAQ entry is incorrect. If you make your SERIAL column the PRIMARY KEY of the table, however, a UNIQUE index will be created. -- Mike Rylander mrylander@gmail.com GPLS -- PINES Development Database Developer http://open-ils.org
Documentation at http://www.postgresql.org/docs/8.0/interactive/datatype.html#DATATYPE-SERIAL says: "In most cases you would also want to attach a UNIQUE or PRIMARY KEY constraint to prevent duplicate values from being inserted by accident, but this is not automatic." and "*Note: * Prior to PostgreSQL 7.3, serial implied UNIQUE. This is no longer automatic. If you wish a serial column to be in a unique constraint or a primary key, it must now be specified, same as with any other data type." Miroslav Šulc, CEO StartNet s.r.o. ---------------------------------------------------- Vrchlického 161/5, Praha 5, 150 00, Česká republika ---------------------------------------------------- ICQ: 60144073 web: www.startnet.cz, www.novavystavba.cz ---------------------------------------------------- mobil: +420 603 711 413 telefon: +420 257 225 602 ---------------------------------------------------- Sam Adams wrote: >I read at http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.1 >that when a serial is created then an index is created on the column. >However I can't seem to find this anywhere in the PoistgreSQL manual. Is >this true? Thanks. > >-Sam > >---------------------------(end of broadcast)--------------------------- >TIP 7: don't forget to increase your free space map settings > >
Вложения
On Sun, 2005-02-27 at 12:54 +0000, Sam Adams wrote: > I read at http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.1 > that when a serial is created then an index is created on the column. > However I can't seem to find this anywhere in the PoistgreSQL manual. Is > this true? Thanks. no, this is not true. howewer defining the serial column as UNIQUE will. test=# create table q2 (a serial unique,b text); NOTICE: CREATE TABLE will create implicit sequence "q2_a_seq" for "serial" column "q2.a" NOTICE: CREATE TABLE / UNIQUE will create implicit index "q2_a_key" for table "q2" CREATE TABLE test=# feel free to inform the webmaster of that site, that the FAQ has been updated since 2002. here is a more authoritative copy: http://www.postgresql.org/files/documentation/faqs/FAQ.html gnari
"Sam Adams" <samadams@myfastmail.com> writes: > I read at http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.1 > that when a serial is created then an index is created on the column. > However I can't seem to find this anywhere in the PoistgreSQL manual. Is > this true? Thanks. It was once true. Perhaps you should be reading a more up-to-date copy of the FAQ. regards, tom lane