Greg Stark wrote: > On Sat, May 30, 2009 at 1:11 PM, Bruce Momjian <br...@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 test psql (8.4beta2) Type "help" for help. test=> select * from tsvector_test; x ---------------- 'c' 'bb' 'aaa' (1 row) test=> test=> \q $ psql test < /u/postgres/rebuilt_tsvector_tables.txt ALTER TABLE ALTER TABLE ALTER TABLE $ psql test psql (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 <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers