Thread: Serial Vs Sequence

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 ?


Thanks & Regards

Adarsh Sharma

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>:
> 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 ?
>
>
> Thanks & Regards
>
> Adarsh Sharma
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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.