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

From: Fernando Schapachnik <fernando(at)mecon(dot)gov(dot)ar>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Is this a bug? (changing sequences in default value)
Date: 2008-05-09 12:55:02
Message-ID: 20080509125502.GB1160@bal740r0.mecon.gov.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

En un mensaje anterior, Merlin Moncure escribió:
> 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

Hi, Merlin. Thanks for the tip, but it doesn't work. Every variation
of this syntax I tried gives me error as, apparently, it should:

\h ALTER SEQUENCE
Command: ALTER SEQUENCE
Description: change the definition of a sequence generator
Syntax:
ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO
MAXVALUE ]
[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

Thanks again!

Fernando.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2008-05-09 13:48:07 Re: Is this a bug? (changing sequences in default value)
Previous Message Merlin Moncure 2008-05-09 12:51:26 Re: Using Epoch to save timestamps in 4 bytes?