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

Re: [GENERAL] Use of Serial Datatype and Sequen

From: Dave Page <dpage(at)vale-housing(dot)co(dot)uk>
To: "'Jean-Michel POURE'" <jm(dot)poure(at)freesurf(dot)fr>, pgsql-general(at)postgresql(dot)org
Cc: pgadmin-hackers(at)postgresql(dot)org
Subject: Re: [GENERAL] Use of Serial Datatype and Sequen
Date: 2001-11-06 08:17:05
Message-ID: AA30E7BCCA5C1D4E88A231900F8325C00C42@dogbert.vale-housing.co.uk (view raw or flat)
Thread:
Lists: pgadmin-hackerspgsql-general

> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm(dot)poure(at)freesurf(dot)fr] 
> Sent: 06 November 2001 07:59
> To: pgsql-general(at)postgresql(dot)org
> Cc: pgadmin-hackers(at)postgresql(dot)org
> Subject: Re: [pgadmin-hackers] [GENERAL] Use of Serial 
> Datatype and Sequence Issue
> 
> 
> At 00:28 02/11/01 +0000, you wrote:
> >I am new to postgres and am in the process of creating a new 
> database 
> >of several 100 tables.  Most of the tables have a serial 
> datatype. When 
> >the table is created psql says it is creating an implicit sequence.  
> >Yet when I drop the table and even when I drop the database the 
> >sequence is still there. When I try to re-create the table it fails 
> >beacuse the sequence already exists.  This same behaviour exists for 
> >indexes.  How can I get rid of any sequences or indexes that 
> have been 
> >created.  I would prefer not to explicitly drop each one.  Ideally I 
> >would like to drop the table and have all related indexes, sequences 
> >dropped.
> 
> pgAdmin2 knows how to drop indexes when dropping a table: 
> http://pgadmin.postgresql.org
> It might not be interesting to drop a sequence as it can be used by 
> multiple tables.
> 
> Dave: do you think pgAdmin2 should drop a sequence when 
> dropping a table?

No I don't, I think you're correct. I'm surprised by the comment that
indexes are left orphaned though - pgAdmin drops them first (well the dev
code does) but that's to maintain the revision log correctly - I thought
PostgreSQL had been dropping them as well since about v6.5(?):

helpdesk=# create table widget(id serial);

NOTICE:  CREATE TABLE will create implicit sequence 'widget_id_seq' for
SERIAL column 'widget.id'

NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'widget_id_key' for
table 'widget'

CREATE

helpdesk=# \d widget

                             Table "widget"

 Attribute |  Type   |                     Modifier

-----------+---------+---------------------------------------------------

 id        | integer | not null default nextval('"widget_id_seq"'::text)

Index: widget_id_key


helpdesk=# \di widget             
        List of relations         
     Name      | Type  |  Owner   
---------------+-------+----------
 widget_id_key | index | postgres 
(1 row)                           

helpdesk=# drop table widget;            
DROP                                     
helpdesk=# \d widget                     
Did not find any relation named "widget".
helpdesk=# \di widget                    
No matching relations found.            

That was on 7.1.2. There are no entries left in pg_index either...

Regards, Dave.


pgadmin-hackers by date

Next:From: Tom LaneDate: 2001-11-06 18:54:09
Subject: Re: Use of Serial Datatype and Sequence Issue
Previous:From: Jean-Michel POUREDate: 2001-11-06 07:58:40
Subject: Re: [GENERAL] Use of Serial Datatype and Sequence Issue

pgsql-general by date

Next:From: Jean-Michel POUREDate: 2001-11-06 08:19:54
Subject: Re: Drop column and multiple db query
Previous:From: Carlo F. FlorendoDate: 2001-11-06 08:12:13
Subject: pg_dump error

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