Re: Is this a bug? (changing sequences in default value)

On Thu, May 8, 2008 at 7:52 AM, Fernando Schapachnik
<fschapachnik(at)mecon(dot)gov(dot)ar> wrote:
> Pg 8.1.11, I try to change sequences as default value of a table, then
> remove old sequence:
>
> # \d table1
> Table "table1"
> Column | Type | Modifiers
> --------+---------+---------------------------------------------------------------
> id | integer | not null default nextval('table1_id_seq'::regclass)
> nombre | text | not null
> Indexes:
> "table1_pkey" PRIMARY KEY, btree (id)
>
> # ALTER TABLE table1 alter column id set default nextval('newseq_id_seq');
> ALTER TABLE
>
> # \d table1
> Table "table1"
> Column | Type | Modifiers
> --------+---------+---------------------------------------------------------------
> id | integer | not null default nextval('newseq_id_seq'::regclass)
> nombre | text | not null
> Indexes:
> "table1_pkey" PRIMARY KEY, btree (id)
>
> # drop SEQUENCE table1_id_seq ;
> ERROR: cannot drop sequence table1_id_seq because table
> table1 column id requires it
> HINT: You may drop table table1 column id instead.
>
> Am I doing something wrong?

yes and no when you created the table initially you probably made it
a 'serial' column which set up the ownership that prevents the drop
operation. that ownership did not go away when you altered the
default to the new serial.

to fix this,
alter sequence sequence table1_id_seq owned by none; -- now you can drop

merlin