Re: best practice transitioning from one datatype to another

I'm stumped-- at least for an easy way to do this.

When referencing the uniqueidentifier data type in PostgreSQL 8.1 (now uuid, because of an UPDATE to the pg_ tables) in function definitions in schemas not in the search path, one must reference the data type as "public.uniqueidentifier" (ahem. "public.uuid"). This was done because the query planner couldn't with 100% certainty determine type equality (i.e. for foreign key constraints, comparisons in WHERE clauses...), so it decided to use sequential scans where index scans would have been most appropriate.

When I reload to PostgreSQL 8.4, it doesn't understand public.uuid. So, the only way I can think of to get those functions back into the database is to use a restore list, and keep them from being inserted in the first place. Then, one-at-a-time, re-create them manually. There must be a better way, though!

I'm sure this only my second of several more hurdles to overcome before I'm finished with the transition.

Your wisdom will be appreciated!

CG

________________________________
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: CG <cgg007(at)yahoo(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Sent: Wednesday, July 15, 2009 7:05:47 PM
Subject: Re: [GENERAL] best practice transitioning from one datatype to another

CG <cgg007(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