Обсуждение: Dropping tables

Поиск
Список
Период
Сортировка

Dropping tables

От
Hrvoje Niksic
Дата:
DROP TABLE does not seem to allow me to fully drop a table.  Example:

test=# CREATE TABLE x (id SERIAL);
NOTICE:  CREATE TABLE will create implicit sequence 'x_id_seq' for SERIAL column 'x.id'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'x_id_key' for table 'x'CREATE

The notices look ominous, but I understand their meaning.  OK.  Now
say I want to drop the table:

test=# DROP TABLE x;
DROP

Cool.  And now I want to create an `x' table again:

test=# CREATE TABLE x (id SERIAL);
NOTICE:  CREATE TABLE will create implicit sequence 'x_id_seq' for SERIAL column 'x.id'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'x_id_key' for table 'x'
ERROR:  Relation 'x_id_seq' already exists

Huh?  What is this?  Oh, x_id_key somehow survived DROP TABLE.
Bummer.  Maybe I can use DROP INDEX to drop it?

test=# DROP INDEX x_id_seq;
ERROR:  relation "x_id_seq" is of type "S"

Oh, x_id_seq is of type "S" -- of course I can't delete it.  That was
a joke.  Seriously, what am I supposed to do to get rid of it?

Or, better said, what do I do to completely and totally delete a
table?


I'm using what seems to be the latest postgresql package in Debian
"unstable" distribution.  Version information follows:

test=# SELECT version();
                            version
---------------------------------------------------------------
 PostgreSQL 7.0.0 on i686-pc-linux-gnu, compiled by gcc 2.95.2
(1 row)

$ dpkg -s post

Re: Dropping tables

От
Ron Peterson
Дата:
Hrvoje Niksic wrote:
>
> DROP TABLE does not seem to allow me to fully drop a table.  Example:
>
> test=# CREATE TABLE x (id SERIAL);
> NOTICE:  CREATE TABLE will create implicit sequence 'x_id_seq' for SERIAL column 'x.id'
> NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'x_id_key' for table 'x'CREATE
>
> Huh?  What is this?  Oh, x_id_key somehow survived DROP TABLE.
> Bummer.  Maybe I can use DROP INDEX to drop it?

Close.  Try DROP SEQUENCE.

________________________
Ron Peterson
rpeterson@yellowbank.com

Re: Dropping tables

От
Hrvoje Niksic
Дата:
Ron Peterson <rpeterson@yellowbank.com> writes:

> > Huh?  What is this?  Oh, x_id_key somehow survived DROP TABLE.
> > Bummer.  Maybe I can use DROP INDEX to drop it?
>
> Close.  Try DROP SEQUENCE.

It works, thanks.  Are there other things if I need to watch out for
when dropping tables?

Re: Dropping tables

От
Ed Loehr
Дата:
Ron Peterson wrote:
>
> Hrvoje Niksic wrote:
> >
> > DROP TABLE does not seem to allow me to fully drop a table.  Example:
> >
> > test=# CREATE TABLE x (id SERIAL);
> > NOTICE:  CREATE TABLE will create implicit sequence 'x_id_seq' for SERIAL column 'x.id'
> > NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'x_id_key' for table 'x'CREATE
> >
> > Huh?  What is this?  Oh, x_id_key somehow survived DROP TABLE.
> > Bummer.  Maybe I can use DROP INDEX to drop it?
>
> Close.  Try DROP SEQUENCE.

And this is a known gotcha (I now actually consider it a feature, if a bit unintuitive):

http://www.postgresql.org/docs/postgres/datatype.htm#AEN946

Regards,
Ed Loehr