Re: best practice transitioning from one datatype to another

On Jul 16, 8:05 am, t(dot)(dot)(dot)(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) wrote:
> CG <cgg(dot)(dot)(dot)(at)yahoo(dot)com> writes:
> > While transitioning from 8.1 to 8.4, I need to transition to the internal UUID type in place of the contrib/uniqueidentifier module. I've built the database around uniqueidentifier, so nearly every table has one column of that data type. It's going to be tedious to
> > ALTER TABLE foo ALTER COLUMN bar SET DATA TYPE uuid;
> > ...repeat 600 times...
> > I'll also have to drop and reload the views and the rules on tables. It'll be tedious even if the tables have no data in them.
> > Can anyone recommend a better/faster way to make the transition?
>
> Couldn't you rename the type to uuid in the 8.1 database before you
> dump?
>
>                         regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Just an idea - don't know if it works, first try on a test server:
1. Dump the complete database into text format (pg_dump --format=t)
2. Do a search and replace from "uniqueidentifier" to "uuid"
3. Reimport

Best Regards,
Arndt Lehmann