Thread: diff's between creations of tables

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...

They are both the same, as far as I know.  I usually make my table
definition file like this:

DROP SEQUENCE mytable_number_seq;
DROP TABLE mytable;
CREATE TABLE mytable (
       number SERIAL
);

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