From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Daniel CAUNE <d.caune@free.fr>
Cc: pgsql-sql@postgresql.org
Subject: Re: Sequence vs Serial
Date: Sun, 01 Apr 2007 02:58:28 -0500
Message-ID: <1175414308.8365.15.camel@localhost.localdomain> (raw)
In-Reply-To: <000001c77412$6a6e10e0$0b00a8c0@tedy>
References: <000001c77412$6a6e10e0$0b00a8c0@tedy>
Daniel CAUNE <d.caune@free.fr> Said:
> I was wondering when it is better to choose sequence, and when
> it is better to use serial. The serial type is a sequence with
> default parameters
(http://www.postgresql.org/docs/8.2/static/datatype-numeric.html#DATATYPE-SERIAL). > Actually, I never use serial – I prefer sequence for some
> reasons that I explain later in this electronic mail – but I may
> be wrong. It’s never late to learn!
From the psql command line:
\h alter sequence
Command: ALTER SEQUENCE
Description: change the definition of a sequence generator
Syntax:
ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO
MAXVALUE ]
[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
\h create sequence
Command: CREATE SEQUENCE
Description: define a new sequence generator
Syntax:
CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO
MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
Basically, the only thing that I see you can't define with alter is to
make it a temp sequence, which wouldn't make a whole lot of sequence.
One reason for using serial versus sequence is that a serial gives you
automatic dependency tracking. If you create a table with a serial and
drop the table, the sequence underlying the serial type is automagically
deleted for you.
OTOH, if you are gonna have two or more tables share a sequence, then it
might be better to not use a serial to start it, as you might decide to
delete the table that created the sequence and things would break in
strange ways.
Either way, I'm just glad PostgreSQL's sequences aren't as difficult to
alter as Oracles. Read the post at the bottom of this page to gape in
wonder at one of the many hoops I have to jump through when I use that
bothersome database: http://www.techonthenet.com/oracle/sequences.php
urg.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: smarlowe@g2switchworks.com, d.caune@free.fr
Subject: Re: Sequence vs Serial
In-Reply-To: <1175414308.8365.15.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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