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@scw.org>
Director of Information Systems, Support Center of Washington