Thread: What is the best practise for "autonumbering"..?

Using Postgres' own serial type or handling the sequences manually?


Regards,

BTJ



-----------------------------------------------------------------------------------------------
Bjørn T Johansen (BSc,MNIF)
Executive Manager
btj@havleik.no                  Havleik Consulting
Phone : +47 67 54 15 17         Conradisvei 4
Fax : +47 67 54 13 91           N-1338 Sandvika
Cellular : +47 926 93 298       http://www.havleik.no
-----------------------------------------------------------------------------------------------
"The stickers on the side of the box said "Supported Platforms: Windows
98, Windows NT 4.0,
Windows 2000 or better", so clearly Linux was a supported platform."
-----------------------------------------------------------------------------------------------

On Tue, May 06, 2003 at 01:09:13PM +0200, Bjorn T Johansen wrote:
> Using Postgres' own serial type or handling the sequences manually?

Use the SERIAL type.  Just create a column with that type and omit it on
INSERT statements -- that way, the default value will be assumed, which
is the next value for the sequence.  You can't have trouble this way.

Note that you can also specify a value for the column in an INSERT (or
UPDATE) statement, and in that case the sequence won't be modified,
which can be a problem later when the sequence hits that value.  You can
also set the column to a number lower than the current value of the
sequence, of course.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Dios hizo a Adán, pero fue Eva quien lo hizo hombre.

On Tuesday 06 May 2003 12:09, Bjorn T Johansen wrote:
> Using Postgres' own serial type or handling the sequences manually?
>

    It does not make a lot of difference. Although creating your table with a
single CREATE TABLE command is nice its not quite as flexable as doing it by
hand.

CREATE TABLE test (id serial primary key);

Is the same as

CREATE SEQUENCE test_id_seq;
CREATE TABLE test (id int);
ALTER TABLE test alter id set default nextval('test_id_seq');
ALTER TABLE test alter id set not null;
ALTER TABLE test add constraint test_id_pk primary key (id);

you could join one or two of them together but the point is that the second
version is alot more flexable you get to control the sequence but you have to
think about it more. Which is probably a good thing as it encorrages you to
think properly about you database design.
    However try to do it without drop columns too much as this wastes space
internally and your going to end up dumping and rebuilding before too long.
People who have used older clients will have seen drop column does not
actually delete the column or any data just hide it!

Peter Childs


>
> Regards,
>
> BTJ
>
>
>
> ---------------------------------------------------------------------------
>-------------------- Bjørn T Johansen (BSc,MNIF)
> Executive Manager
> btj@havleik.no                  Havleik Consulting
> Phone : +47 67 54 15 17         Conradisvei 4
> Fax : +47 67 54 13 91           N-1338 Sandvika
> Cellular : +47 926 93 298       http://www.havleik.no
> ---------------------------------------------------------------------------
>-------------------- "The stickers on the side of the box said "Supported
> Platforms: Windows 98, Windows NT 4.0,
> Windows 2000 or better", so clearly Linux was a supported platform."
> ---------------------------------------------------------------------------
>--------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

On Tue, 6 May 2003, Peter Childs wrote:

> On Tuesday 06 May 2003 12:09, Bjorn T Johansen wrote:
> > Using Postgres' own serial type or handling the sequences manually?
> >
>
>     It does not make a lot of difference. Although creating your table with a
> single CREATE TABLE command is nice its not quite as flexable as doing it by
> hand.
>
> CREATE TABLE test (id serial primary key);
>
> Is the same as
>
> CREATE SEQUENCE test_id_seq;
> CREATE TABLE test (id int);
> ALTER TABLE test alter id set default nextval('test_id_seq');
> ALTER TABLE test alter id set not null;
> ALTER TABLE test add constraint test_id_pk primary key (id);
>
> you could join one or two of them together but the point is that the second
> version is alot more flexable you get to control the sequence but you have to
> think about it more. Which is probably a good thing as it encorrages you to
> think properly about you database design.

I'll bite; why does this make one think about the DB design more than the
first?

Until I realised 7.3 named it's serial sequences in a very sensible manner I
was preparing to do:

CREATE SEQUENCE test_id_seq;
CREATE TABLE test ( id integer default nextval('test_id_seq') primary key );

which I can see is worse than your second version only if you are interested in
having a note of the primary key's name and the not null constraint.


> However try to do it without drop columns too much as this wastes space
> internally and your going to end up dumping and rebuilding before too long.
> People who have used older clients will have seen drop column does not
> actually delete the column or any data just hide it!

And I don't see where the DROP COLUMNs comment comes into this.


--
Nigel J. Andrews