pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSerial and Index
7+ messages / 7 participants
[nested] [flat]
* Serial and Index
@ 2005-02-27 12:54 Sam Adams <samadams@myfastmail.com>
0 siblings, 6 replies; 7+ messages in thread
From: Sam Adams @ 2005-02-27 12:54 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Serial and Index
@ 2005-02-27 16:28 Michael Fuhr <mike@fuhr.org>
parent: Sam Adams <samadams@myfastmail.com>
5 siblings, 0 replies; 7+ messages in thread
From: Michael Fuhr @ 2005-02-27 16:28 UTC (permalink / raw)
To: Sam Adams <samadams@myfastmail.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Serial and Index
@ 2005-02-27 16:37 Bruno Wolff III <bruno@wolff.to>
parent: Sam Adams <samadams@myfastmail.com>
5 siblings, 0 replies; 7+ messages in thread
From: Bruno Wolff III @ 2005-02-27 16:37 UTC (permalink / raw)
To: Sam Adams <samadams@myfastmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Serial and Index
@ 2005-02-27 16:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sam Adams <samadams@myfastmail.com>
5 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2005-02-27 16:59 UTC (permalink / raw)
To: Sam Adams <samadams@myfastmail.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Serial and Index
@ 2005-02-27 17:12 Ragnar Hafstað <gnari@simnet.is>
parent: Sam Adams <samadams@myfastmail.com>
5 siblings, 0 replies; 7+ messages in thread
From: Ragnar Hafstað @ 2005-02-27 17:12 UTC (permalink / raw)
To: Sam Adams <samadams@myfastmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Serial and Index
@ 2005-02-27 17:31 Miroslav Šulc <miroslav.sulc@startnet.cz>
parent: Sam Adams <samadams@myfastmail.com>
5 siblings, 0 replies; 7+ messages in thread
From: Miroslav Šulc @ 2005-02-27 17:31 UTC (permalink / raw)
To: Sam Adams <samadams@myfastmail.com>; +Cc: pgsql-sql
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
>
>
Attachments:
miroslav.sulc.vcf (text/x-vcard, 401B) download
begin:vcard
fn;quoted-printable:Miroslav =C5=A0ulc
n;quoted-printable:=C5=A0ulc;Miroslav
org:StartNet s.r.o.
adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika
email;internet:miroslav.sulc@startnet.cz
title:CEO
tel;work:+420 257 225 602
tel;cell:+420 603 711 413
x-mozilla-html:TRUE
url:http://www.startnet.cz
version:2.1
end:vcard
* Re: Serial and Index
@ 2005-02-27 17:43 Mike Rylander <mrylander@gmail.com>
parent: Sam Adams <samadams@myfastmail.com>
5 siblings, 0 replies; 7+ messages in thread
From: Mike Rylander @ 2005-02-27 17:43 UTC (permalink / raw)
To: Sam Adams <samadams@myfastmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-02-27 17:43 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-27 12:54 Serial and Index Sam Adams <samadams@myfastmail.com>
2005-02-27 16:28 ` Michael Fuhr <mike@fuhr.org>
2005-02-27 16:37 ` Bruno Wolff III <bruno@wolff.to>
2005-02-27 16:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-27 17:12 ` Ragnar Hafstað <gnari@simnet.is>
2005-02-27 17:31 ` Miroslav Šulc <miroslav.sulc@startnet.cz>
2005-02-27 17:43 ` Mike Rylander <mrylander@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox