From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: alter table schema, default sequences stay the same |
Date: | 2010-06-29 12:49:28 |
Message-ID: | AANLkTiknHGUPqX5CHpEQuTrhH1sn5TT8fDV15sfd1yi_@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Jun 29, 2010 at 8:20 AM, A. Kretschmer
<andreas(dot)kretschmer(at)schollglas(dot)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...
From | Date | Subject | |
---|---|---|---|
Next Message | Arnaud Lesauvage | 2010-06-29 12:58:11 | Re: No quotes in output of "psql \copy CSV" |
Previous Message | Sam Mason | 2010-06-29 12:40:39 | Re: No quotes in output of "psql \copy CSV" |