Friday, May 9, 2008

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

En un mensaje anterior, Merlin Moncure escribió:
> On Thu, May 8, 2008 at 7:52 AM, Fernando Schapachnik
> <fschapachnik@mecon.gov.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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

No comments: