Обсуждение: 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
>