Re: Sequence vs Serial

Daniel CAUNE <d(dot)caune(at)free(dot)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.