Dropping tables
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
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'CREATEHuh? 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
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?
Import Notes
Reply to msg id not found: RonPeterson'smessageofWed14Jun2000154951-0400
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'CREATEHuh? 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