Greg Stark wrote:
> On Sat, May 30, 2009 at 1:11 PM, Bruce Momjian <bruce@momjian.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.
I have implemented Greg's ideas using ::text::tsvector casting and it
worked:
$ psql testpsql (8.4beta2)Type "help" for help.test=> select * from tsvector_test; x---------------- 'c' 'bb'
'aaa'(1row)test=>test=> \q
$ psql test < /u/postgres/rebuilt_tsvector_tables.txtALTER TABLEALTER TABLEALTER TABLE$ psql testpsql (8.4beta2)Type
"help"for help.test=> select * from tsvector_test; x---------------- 'aaa' 'bb' 'c'(1 row)
Here is the script that groups multiple tsvector columns into a single
ALTER TABLE:
ALTER TABLE "public"."tsvector_test" ALTER COLUMN "x" TYPE tsvector USING "x"::text::tsvector;ALTER TABLE
"public"."tsvector_test2" ALTER COLUMN "x" TYPE tsvector USING "x"::text::tsvector, ALTER COLUMN "y" TYPE tsvector
USING"y"::text::tsvector;ALTER TABLE "public"."tsvector_test3" ALTER COLUMN "x" TYPE tsvector USING
"x"::text::tsvector, ALTER COLUMN "y" TYPE tsvector USING "y"::text::tsvector, ALTER COLUMN "z" TYPE tsvector USING
"z"::text::tsvector;
I knew I was going to have to deal with this optimization but didn't
realize ALTER TABLE already supported multiple TYPE changes.
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
