OK. This is working in my test environment.
I changed the schema name
I changed the binary and text description in the attrdef table.
I subtract 1 from the number both before the [ and after the ] because
my new schema has 1 letter less then the old schema.
It seems to be working.
Can anybody think of a possible problem with this?
Note: I am not planning on doing this more then once. If it will not
break anything this time, I don't care that it may not work in the future.
--changes schema name from public to stock
update pg_attrdef set
adsrc=replace(adsrc,'public.','stock.'),adbin=replace(adbin,'112 117 98
108 105 99 46','115 116 111 99 107 46');
--function to change the number of characters in the binary string
create or replace function fixattrs(text) returns text as
return string.joinfields(x," ")
--update the binary string with the new numbers.
update pg_attrdef set adbin=fixattrs(adbin) where adsrc like 'nextval%'
Sim Zacks wrote:
> "I may not be a smart man, but I do know what love is"
> Of course I did this on a database that I didn't care about. This is my
> development machine. If it actually fully worked after testing, I would
> have considered moving it into production code.
> Is there a more approved way of doing this, besides upgrading the server?
> >Did you start a fresh session after modifying the catalog?
> Is that all I needed to do? As I mentioned in another post, I screwed up
> something else in there and I'll be playing with that tomorrow. If
> there's an easier way to do it, please let me know.
> Tom Lane wrote:
>> Sim Zacks <sim(at)compulab(dot)co(dot)il> writes:
>>> PostGreSQL 8.01 Gentoo
>>> I renamed my schema from public to stock, and then I found out it
>>> didn't change the schemas of the sequences. After searching through
>>> the archives, I found that this was on a bug list and would probably
>>> be fixed in version 8.2
>> Actually, it's fixed in 8.1.
>>> I needed to fix the sequences in any case, so I tried to do it
>>> manually using this code:
>>> update pg_attrdef set
>>> adsrc=replace(adsrc,'public.','stock.'),adbin=replace(adbin,'112 117
>>> 98 108 105 99 46','115 116 111 99 107 46');
>> Like to live dangerously, eh? Hope you weren't doing this on a database
>> containing data you cared about.
>>> However, when I try to insert into the table I get an error that
>>> public.sequence name is not found
>> Did you start a fresh session after modifying the catalog? I don't
>> think that cached relation descriptors will react to manual hacks
>> on pg_attrdef.
>> regards, tom lane
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: don't forget to increase your free space map settings
In response to
pgsql-general by date
|Next:||From: Doug McNaught||Date: 2006-01-31 14:05:47|
|Subject: Re: PostgreSQL + hibernate|
|Previous:||From: Thomas Hallgren||Date: 2006-01-31 11:51:25|
|Subject: Re: New project launched : PostgreSQL GUI|