Re: How to drop sequence? - Mailing list pgsql-general

You're right I am getting the same results. I created the same table with:
   create table category (
      category_id serial not null primary key,
      category_name character varying(100) not null
   );

  alter table category alter column category_id drop default;
  ALTER TABLE

   drop sequence public.category_category_id_seq;
   ERROR:  cannot drop sequence category_category_id_seq because table
category column category_id requires it
   HINT:  You may drop table category column category_id instead.

and it won't let me drop the sequence, even if I drop the default for
the column first. Does anyone know if this is the way this is supposed
to work, and if so, how to remove the dependency on it from the column
category_id?

Ron

ps you should (also) reply to the list.

Igor Kryltsov wrote:

>Hi Ron,
>
>Thank you for your mail.
>
>I tried - result is confusing (there is no default on a column but sequence still can not be dropped)
>I can not believe that column has to be dropped in order to remove sequence which is nothing to do with column after
droppingdefault on a column. 
>
>
>
>amity_wa=# DROP SEQUENCE category_category_id_seq CASCADE;
>ERROR:  Cannot drop sequence category_category_id_seq because table category column category_id requires it
>        You may drop table category column category_id instead
>
>amity_wa=# \d category
>                                          Table "public.category"
>    Column     |          Type          |                             Modifiers
>---------------+------------------------+-------------------------------------------------------------------
> category_id   | integer                | not null default nextval('public.category_category_id_seq'::text)
> category_name | character varying(100) | not null
>Indexes: category_pkey primary key btree (category_id)
>
>amity_wa=# alter table category alter column category_id drop default;
>ALTER TABLE
>
>amity_wa=# DROP SEQUENCE category_category_id_seq CASCADE;
>ERROR:  Cannot drop sequence category_category_id_seq because table category column category_id requires it
>        You may drop table category column category_id instead
>
>amity_wa=# \d category
>              Table "public.category"
>    Column     |          Type          | Modifiers
>---------------+------------------------+-----------
> category_id   | integer                | not null
> category_name | character varying(100) | not null
>Indexes: category_pkey primary key btree (category_id)
>
>
>
>"Ron St-Pierre" <rstpierre@syscor.com> wrote in message news:<40435C84.4010401@syscor.com>...
>
>
>>Igor Kryltsov wrote:
>>
>>
>>
>>>Hi,
>>>
>>>I have table:
>>>
>>>
>>># \d category;
>>> category_id   | integer                | not null default
>>>nextval('public.category_category_id_seq'::text)
>>>category_name | character varying(100) | not null
>>>Indexes: category_pkey primary key btree (category_id)
>>>
>>>My goal is to remove sequence from category_id column and remove it after
>>>
>>>
>>>from DB.
>>
>>
>>>First I tried:
>>>
>>>DROP SEQUENCE  category_category_id_seq - fails saying that table category
>>>column category_id uses it
>>>
>>>Than I tried:
>>>
>>>ALTER TABLE category ALTER COLUMN category_id DROP DEFAULT;
>>>
>>>Now category_id column is shown as integer not null only but :
>>>
>>>DROP SEQUENCE  category_category_id_seq - fails saying that table category
>>>column category_id uses it again
>>>
>>>
>>>Any suggestions?
>>>
>>>Thank you,
>>>
>>>
>>>Igor
>>>
>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 5: Have you checked our extensive FAQ?
>>>
>>>              http://www.postgresql.org/docs/faqs/FAQ.html
>>>
>>>
>>>
>>>
>>Try
>>
>>DROP SEQUENCE category_category_id_seq CASCADE;
>>
>>Ron
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 9: the planner will ignore your desire to choose an index scan if your
>>      joining column's datatypes do not match
>>
>>
>>
>
>
>
>