Обсуждение: is this a bug ?
hi, I'm facing this problem DROP TABLE test; DROP TABLE new_test; CREATE TABLE test (id serial NOT NULL UNIQUE, nom varchar(32)); INSERT INTO test ("nom") values ('cedric'); INSERT INTO test ("nom") values ('felix'); INSERT INTO test ("nom") values ('julien'); CREATE TABLE new_test (id serial NOT NULL UNIQUE, nom varchar(32)); INSERT INTO new_test SELECT * FROM test; ALTER TABLE new_test ALTER COLUMN id SET DEFAULT nextval('public.test_id_seq'); DROP TABLE test; ALTER TABLE new_test RENAME TO test; ALTER TABLE new_test_id_key RENAME TO test_id_key; DROP SEQUENCE new_test_id_seq ; -- this give an error... is this normal is this the normal behaviour ? Ced. -- Cedric BRINER
On 6/16/05, Cedric BRINER <work@infomaniak.ch> wrote: > hi, > > I'm facing this problem > > DROP TABLE test; > DROP TABLE new_test; > CREATE TABLE test (id serial NOT NULL UNIQUE, nom varchar(32)); > INSERT INTO test ("nom") values ('cedric'); > INSERT INTO test ("nom") values ('felix'); > INSERT INTO test ("nom") values ('julien'); > > CREATE TABLE new_test (id serial NOT NULL UNIQUE, nom varchar(32)); > INSERT INTO new_test SELECT * FROM test; > Why create the table with a serial field if you're going to drop the seq anyway just making it integer and this way you avoid your problem? > ALTER TABLE new_test ALTER COLUMN id SET DEFAULT nextval('public.test_id_seq'); > DROP TABLE test; > > ALTER TABLE new_test RENAME TO test; > ALTER TABLE new_test_id_key RENAME TO test_id_key; > > DROP SEQUENCE new_test_id_seq ; > -- this give an error... is this normal > > is this the normal behaviour ? > -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;)
This is a normal error. If you want to remove the sequence first you have to remove the default value from the sequenced field. remember serial means int4 with a default of get next serial number. ALTER TABLE test ALTER COLUMN test_id_key DROP DEFAULT; After that you could probably drop the sequence without a problem "Cedric BRINER" <work@infomaniak.ch> wrote in message news:20050616175239.GA23283@obs.unige.ch... > hi, > > I'm facing this problem > > DROP TABLE test; > DROP TABLE new_test; > CREATE TABLE test (id serial NOT NULL UNIQUE, nom varchar(32)); > INSERT INTO test ("nom") values ('cedric'); > INSERT INTO test ("nom") values ('felix'); > INSERT INTO test ("nom") values ('julien'); > > CREATE TABLE new_test (id serial NOT NULL UNIQUE, nom varchar(32)); > INSERT INTO new_test SELECT * FROM test; > > ALTER TABLE new_test ALTER COLUMN id SET DEFAULT nextval('public.test_id_seq'); > DROP TABLE test; > > ALTER TABLE new_test RENAME TO test; > ALTER TABLE new_test_id_key RENAME TO test_id_key; > > DROP SEQUENCE new_test_id_seq ; > -- this give an error... is this normal > > is this the normal behaviour ? > > Ced. > -- > > Cedric BRINER > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >