Serial Vs Sequence

On 01/31/11 10:55 PM, Adarsh Sharma wrote:

Dear all,

I am not able to distinct these two datatypes ( Serial and Sequence )
in Postgresql which resembles like auto-increment in Mysql.

Which one gets priority and When ?

The only thing I am able to find is to use SERIAL because if a drop
table occurs , still SEQUENCE memory is not freed, its garbage remains.

However if we define a SERIAL column in a table , implicitly it makes
a sequence.

Could anyone Please describe me the difference and Which to Use When ?

neither is actually a data type.

a sequence is a special table-like object that has auto increment logic
associated with it.

a serial is a pseudotype implemented as an integer field with an
associated sequence.

when you drop a table that has an associated sequence, the sequence is
dropped automatically.

Hello

SEQUENCE isn't datatype. It is a database object. Simple object, that
can to generate unique integer numbers.

SERIAL is a fictive datatype. It can create a own SEQUENCE object and
can create a reference on this object.

pavel=# create sequence aaaa;
CREATE SEQUENCE
Time: 461.883 ms
pavel=# select nextval('aaaa');
nextval
─────────
1
(1 row)

Time: 78.413 ms
pavel=# select nextval('aaaa');
nextval
─────────
2
(1 row)

Time: 12.761 ms

pavel=# create table ggg(a serial);
NOTICE: CREATE TABLE will create implicit sequence "ggg_a_seq" for
serial column "ggg.a"
CREATE TABLE
Time: 91.866 ms
pavel=# \d ggg
Table "public.ggg"
Column │ Type │ Modifiers
────────┼─────────┼─────────────────────────────────────────────────
a │ integer │ not null default nextval('ggg_a_seq'::regclass)

pavel=#

regards

Pavel Stehule

2011/2/1 Adarsh Sharma <adarsh.sharma@orkash.com>: