Re: alter table schema, default sequences stay the same

> I haven't consumed enough caffeine today to recall the details, but
> I think you could have ended up with default expressions like the above
> if the database had been dumped and reloaded from 8.0 or earlier.
> nextval(regclass) was introduced in 8.1 precisely to solve this type
> of problem.
>
>
>> Is there an easy way to modify all the default values now?
>>
> Not especially :-(. You're going to need to run around and change them
> all to the right name. Be sure to lose the ::text bit while at it,
> so it works right the next time.
>
> regards, tom lane
>
This is the query I used to generate the alter stmt for all the defaults
that used public sequences in the new schema

select 'alter table ' || relname || ' alter column ' || attname || ' set
default ' ||
replace(replace(replace(b.adsrc,'public.',''),'::text',''),'::regclass','')
|| ';'
from pg_attribute a
join pg_attrdef b on a.attrelid=b.adrelid and attnum=adnum
join pg_class c on c.oid=a.attrelid
where adsrc like '%nextval((''public.%'
and relnamespace=558726861
order by adsrc

My first database was an 8.0 and at some point we upgraded to 8.2 so
that must have caused the ?corruption?