Re: Restore v. Running COPY/INDEX seperatly - Mailing list pgsql-general

"Oleg Bartunov" <oleg@sai.msu.su> writes:

> On Sun, 26 Aug 2007, Benjamin Arai wrote:
>
>> Hi,
>>
>> So, I built my tables which contains a TSearch2 field by
>>
>> 1. Create table without indexes
>> 2. COPY data into table
>> 3. ALTER TABLE tblMessages ADD COLUMN idxFTI tsvector;
>> 4. UPDATE tblMessages SET idxFTI=to_tsvector('default', strMessage);
>
> vacuum here

Or you could do something tricky and do the update like this which would avoid
the need to vacuum:

ALTER TABLE tblMessages ALTER COLUMN idxFTI TYPE tsvector USING to_tsvector('default, strMesage);

This only works because ALTER TABLE rewrites the table from scratch any time
it does an operation like this. Don't try this if there are transactions
working against the table at the same time (such as a pg_dump!).

Or you could set up a trigger to generate the tsvector when you first load the
data instead of adding it later.


--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com