Re: diff's between creations of tables

On Thu, 26 Jul 2001, G.L. Grobe wrote:

> When creating an incremental and unique id, what are the benefits of using:
>
> CREATE TABLE tablename (colname SERIAL);
>
> instead of :
>
> CREATE SEQUENCE tablename_colname_seq;
> CREATE TABLE tablename
> (colname integer DEFAULT nextval('tablename_colname_seq');
> CREATE UNIQUE INDEX tablename_colname_key on tablename (colname);
>
> One is easier do delete as a dropdb dbname would do it, but anything else I
> should know. Or which one is the general practice, any rules of thumb to
> use, etc...

Same thing.

If you

CREATE TABLE foo (id serial);

PostgreSQL handles this by creating the sequence and index for you.
For the above statement, it does the following:

CREATE SEQUENCE "foo_id_seq" start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1 ;

CREATE TABLE "foo" (
"id" integer DEFAULT nextval('"foo_id_seq"'::text) NOT NULL
);

CREATE UNIQUE INDEX "foo_id_key" on "foo" using btree
("id" "int4_ops" );

[taken right from pg_dump]

Both are deleted the same way:

DROP table foo;
DROP sequence foo_id_seq;

DROPDB dbname will *always* delete everything in a database, assuming
you have permissions to use it.

--
Joel Burton <jburton(at)scw(dot)org>
Director of Information Systems, Support Center of Washington