Re: alter table schema, default sequences stay the same

On Tue, Jun 29, 2010 at 8:20 AM, A. Kretschmer
<andreas.kretschmer@schollglas.com> wrote:
> In response to Sim Zacks :
>> I am using 8.2.17
>>
>> I added a new schema and moved tables into it using
>>
>> ALTER TABLE tblname SET SCHEMA newschema;
>>
>>
>> This moves the sequences referred to by the table to the new schema as
>> is specified by the manual.
>>
>> > Associated indexes, constraints, and sequences owned by table columns
>> > are moved as well.
>>
>> I was very surprised to find that the default nextval functions still
>> refer to the sequence public.sequencename
>>
>> I discovered this when I tried to insert and it told me the sequence
>> does not exist.
>>
>>
>> > id integer NOT NULL DEFAULT
>> > nextval(('public.tblname_id_seq'::text)::regclass)
>> Shouldn't this change automatically as well?
>> Is there an easy way to modify all the default values now?
>
>
>
> Just for info: works well with 8.4:
>
> test=*# create schema bla;
> CREATE SCHEMA
> test=*# create table public.s (i serial);
> NOTICE:  CREATE TABLE will create implicit sequence "s_i_seq" for serial column "s.i"
> CREATE TABLE
> test=*# \d s
>                         Table "public.s"
>  Column |  Type   |                   Modifiers
> --------+---------+-----------------------------------------------
>  i      | integer | not null default nextval('s_i_seq'::regclass)
>
> test=*# alter table s set schema bla;
> ALTER TABLE
> test=*# \d bla.s
>                            Table "bla.s"
>  Column |  Type   |                     Modifiers
> --------+---------+---------------------------------------------------
>  i      | integer | not null default nextval('bla.s_i_seq'::regclass)
>
> test=*# select version();
>                                                version
> --------------------------------------------------------------------------------------------------------
>  PostgreSQL 8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2, 64-bit
> (1 row)

Works in 8.3.9 on ubuntu 9.10...