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...