Re: pg_migrator and an 8.3-compatible tsvector data type

On Sat, May 30, 2009 at 1:11 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> I have discovered a simpler solution using ALTER TABLE and calling a
> conversion function:
>
>        test=> CREATE TABLE tsvector_test(x tsvector);
>        CREATE TABLE
>        test=> ALTER TABLE tsvector_test ALTER COLUMN x TYPE tsvector
>        test-> USING conversion_func(x);
>        ALTER TABLE
>
> No need for a fake data type and the required index infrastructure.

I assume you're putting this in the list of commands to run
post-migration along with any reindex commands etc? Because it will
take a while (still faster than dump/reload i think).

For this case, assuming the new tsvector's output function doesn't get
confused by the old ordering, I think you can just use USING
x::text::tsvector as your conversion expression. For more complex
cases you might need to package up the old output function.

Also note that you'll want to do any other conversions in the same
table at the same time rather than doing multiple conversions.

Also, one gotcha to note is that tsvector data can appear inside
composite data types or arrays. I don't think that's common so perhaps
just a warning in the readme would suffice, but it's something to note
at least.

--
greg