Thread: 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'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
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?
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