Postgres Pro
Downloads
Home   >   mailing lists

Thread: dropping an unused sequence?

dropping an unused sequence?

From
Date:
hi list

during table creation i mistakenly had a SERIAL instead of INTEGER type for
a value. i removed the default value (nextval....) for this field. still i
can't drop the old sequence:

ERROR:  cannot drop sequence kinos_normal_kn_k_id_seq because table
kinos_normal column kn_k_id requires it
HINT:  You may drop table kinoprogramm.kinos_normal column kn_k_id instead.

i know i may drop the field and recreate it, but this is very annoying as
views and foreign keys are already assigned to that particular field. is
there another way to drop the unused sequence?

thanks,
thomas

Re: dropping an unused sequence?

From
Tom Lane
Date:
<me@alternize.com> writes:
> during table creation i mistakenly had a SERIAL instead of INTEGER type for
> a value. i removed the default value (nextval....) for this field. still i
> can't drop the old sequence:

Ideally, SERIAL would prevent you from munging the default expression,
too.  What you are doing represents unwarranted tampering with the
implementation of a data type...

Having said that, if you remove the relevant entry in pg_depend then
you'll be able to drop the sequence.

            regards, tom lane

Re: dropping an unused sequence?

From
Date:
> <me@alternize.com> writes:
>> during table creation i mistakenly had a SERIAL instead of INTEGER type
>> for
>> a value. i removed the default value (nextval....) for this field. still
>> i
>> can't drop the old sequence:
>
> Ideally, SERIAL would prevent you from munging the default expression,
> too.  What you are doing represents unwarranted tampering with the
> implementation of a data type...
>
> Having said that, if you remove the relevant entry in pg_depend then
> you'll be able to drop the sequence.

thanks. i'm using EMS SQL Manager 3. when creating a SERIAL data field, it
is shown as INTEGER afterwards with the "nextval(..)" default value. thats
how and why i was able to make it a "pure" integer ;-)

i'll fill a bug report at ems about this.

thanks for your fast help,
thomas

Re: dropping an unused sequence?

From
Jaime Casanova
Date:
On 1/2/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> <me@alternize.com> writes:
> > during table creation i mistakenly had a SERIAL instead of INTEGER type for
> > a value. i removed the default value (nextval....) for this field. still i
> > can't drop the old sequence:
>
> Ideally, SERIAL would prevent you from munging the default expression,
> too.  What you are doing represents unwarranted tampering with the
> implementation of a data type...
>
> Having said that, if you remove the relevant entry in pg_depend then
> you'll be able to drop the sequence.
>
>                        regards, tom lane
>

why not let ALTER TYPE drop the sequence if one exists?

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)