Postgresql – How to set weights on a Postgres TSVECTOR column

full-text-searchpostgresql

Currently, I'm using Postgres 9.3.5 for full-text search but I don't want to set weight or convert to tsvector on the fly (a lot of articles suggest that, and that just seems like a terrible idea to me). Here's what my table looks like:

CREATE TABLE documents
(
   id serial NOT NULL,
   name character varying(255),
   raw_data text,
   user_id integer,
   active boolean,
   created_at timestamp with time zone,
   updated_at timestamp with time zone,
   search tsvector
)

And here's the query I run to set the tsvector column (search):

UPDATE documents SET search = (setweight(to_tsvector(name), 'A') || setweight(to_tsvector(raw_data), 'B'));

Now, whenever I run a select query, like so:

SELECT name FROM documents WHERE search @@ to_tsquery('some & tokens');

The only results I ever get are if I type in the exact name of the data so for instance, I'd have a hello document so if I use the hello token, I get the result; however, if I type in anything from the raw_data column, nothing shows up. So, for example, I have a row where raw_data contains the sentence "Exhibit B, more information attached". Searching using any of those words comes up with nothing.

I'm trying to do the same thing locally, and it seems to work. The only difference is that I'm running on a windows machine and production is on linux and that the production table is UTF8. Could that make a difference?

Best Answer

Okay so this was dumb of me. The weights work correctly. The issue I was having was because of the forced UTF8 encoding (I simply set "utf8" on the db, didn't rebuild. I set it back to latin1 and it works now).