Skip site navigation (1) Skip section navigation (2)

Re: diff's between creations of tables

From: Joel Burton <jburton(at)scw(dot)org>
To: "G(dot)L(dot) Grobe" <gary(at)grobe(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: diff's between creations of tables
Date: 2001-07-26 09:29:17
Message-ID: Pine.LNX.4.21.0107260523590.12370-100000@olympus.scw.org (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
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


In response to

pgsql-novice by date

Next:From: mikeDate: 2001-07-26 14:11:52
Subject: Postgresql & Redhat 6.2 = memory leak?
Previous:From: G.L. GrobeDate: 2001-07-26 05:40:12
Subject: diff's between creations of tables

pgsql-general by date

Next:From: Ivan UemlianinDate: 2001-07-26 09:47:06
Subject: Some questions on using arrays.
Previous:From: Steve SAUTETNERDate: 2001-07-26 09:19:36
Subject: concurent updates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group