Thread: Serial and Index

Serial and Index

From
"Sam Adams"
Date:
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


Re: Serial and Index

From
Michael Fuhr
Date:
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/


Re: Serial and Index

From
Bruno Wolff III
Date:
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.


Re: Serial and Index

From
Mike Rylander
Date:
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


Re: Serial and Index

From
Miroslav Šulc
Date:
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
>
>

Attachment

Re: Serial and Index

From
Ragnar Hafstað
Date:
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




Re: Serial and Index

From
Tom Lane
Date:
"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