Tsearch2 README File Example: Overview (Continued)

Start a psql session.

psql ts_db;

Need to set the locale for the default tsearch configuration. Look at the lc_* variables.

show lc_ctype;
 
   lc_ctype
-------------
 en_US.UTF-8
(1 row)
    
update pg_ts_cfg set locale = 'en_US.UTF-8' where ts_name = 'default';

Create the table ts_readme table.

CREATE TABLE ts_readme (
    line_number INTEGER PRIMARY KEY,
    line TEXT);

Load file README.tsearch2 with each row as a line number and line.

\! (echo 'COPY ts_readme FROM STDIN;' && cat -n README.tsearch2 && echo '\.') | psql ts_db

Add a column ts_vec of type ts_vector. This column will store a vector of word stems.

ALTER TABLE ts_readme
ADD COLUMN ts_vec tsvector;

Update the ts_vec column using the to_tsvector() function. Use the default tsearch parser and replace null values with empty strings.

UPDATE ts_readme
SET ts_vec = to_tsvector(coalesce(line, ''));