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

From: Peter Childs <Blue(dot)Dragon(at)blueyonder(dot)co(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: What is the best practise for "autonumbering"..?
Date: 2003-05-06 19:26:36
Message-ID: 200305062026.36844.Blue.Dragon@blueyonder.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)havleik(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Egyud Csaba 2003-05-06 19:34:29 PL/pgSQL question - record or %ROWTYPE?
Previous Message Cristian Custodio 2003-05-06 19:26:11 record new is unassigned yet