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

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