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

From: "Nigel J(dot) Andrews" <nandrews(at)investsystems(dot)co(dot)uk>
To: Peter Childs <Blue(dot)Dragon(at)blueyonder(dot)co(dot)uk>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: What is the best practise for "autonumbering"..?
Date: 2003-05-06 19:56:50
Message-ID: Pine.LNX.4.21.0305062051070.13508-100000@ponder.fairway2k.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message scott.marlowe 2003-05-06 20:41:17 Re: Backend memory leakage when inserting
Previous Message Gerhard Hintermayer 2003-05-06 19:43:24 Re: Backend memory leakage when inserting