Re: alter table schema, default sequences stay the same

On 6/29/2010 3:49 PM, Scott Marlowe wrote:
> 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:
>>
>>
>>
> Works in 8.3.9 on ubuntu 9.10...
>
>
In 8.2.17 I just tried and when creating a table and then changing the
schema it works fine.
However, when i looked at the table definition in pgadmin it showed the
type as serial without any defaut nextval.
After I moved its schema, the definition showed a datatype of int and
the default nextval but without any schema qualification for the sequence.
For some reason, (could be because of an upgrade or data restore) all of
my table definitions show default nextval(public.
They were not defined that way, they were defined as serial (if that
makes any difference).

I guess my big question is: how would I change all the default values to
remove the schema qualification. I suppose updating the pg_attrdef table
is not recommended, if it would work at all.

Sim