On 6/29/2010 4:24 PM, A. Kretschmer wrote:
> In response to Scott Marlowe :
>
>> On Tue, Jun 29, 2010 at 8:20 AM, A. Kretschmer
>> <andreas(dot)kretschmer(at)schollglas(dot)com> wrote:
>>
>>>
>>> Just for info: works well with 8.4:
>>>
>> Works in 8.3.9 on ubuntu 9.10...
>>
>>
> I think, this is the problem:
> You have created the table first and later the sequence, like this:
>
>
> test=# create table public.s (i int);
> CREATE TABLE
> test=*# create sequence my_seq;
> CREATE SEQUENCE
> test=*# alter table s alter column i set default nextval('my_seq');
> ALTER TABLE
> test=*# create schema bla;
> CREATE SCHEMA
> test=*# alter table s set schema bla;
> ALTER TABLE
> test=*# \d bla.s
> Table "bla.s"
> Column | Type | Modifiers
> --------+---------+-------------------------------------
> i | integer | default nextval('my_seq'::regclass)
>
>
> You have now a table in schema bla and the sequence still in public. To
> avoid that you have to alter the sequence too:
>
> test=# create table public.s (i int);
> CREATE TABLE
> test=*# create sequence my_seq;
> CREATE SEQUENCE
> test=*# alter table s alter column i set default nextval('my_seq');
> ALTER TABLE
> test=*# alter sequence my_seq owned by s.i;
> ALTER SEQUENCE
> test=*# create schema bla;
> CREATE SCHEMA
> test=*# alter table s set schema bla;
> ALTER TABLE
> test=*# \d bla.s
> Table "bla.s"
> Column | Type | Modifiers
> --------+---------+-----------------------------------------
> i | integer | default nextval('bla.my_seq'::regclass)
>
>
> But i'm not sure if 'alter sequence owned ...' available in 8.2.
>
No. The sequences were all created automatically using the serial type.
When I do the alter table it actually moves the sequences.
The only thing it doesn't do is change the default value. The strange
thing I noticed is that all the default values show public.sequencename.
instead of serial.