CODEWITHSUNDEEP
postgresql
CarbonMan
CarbonMan

Reputation: 4490

Only getting 1 result from postgres tsvector

I am using PostgreSQL 9.3. I have built a dataset with a tsvector field called vector.

Then I execute a query against it

SELECT id, vector, relative_path, title
FROM site_server.indexed_url, plainto_tsquery('english','booking') query
WHERE vector @@ query;

Only 1 row is returned. When I look at the data there are at least 6 rows that would match. How do I get it to retrieve all matching records?

Data file

Upvotes: 1

Views: 199

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51609

Values in vector column in your data sample are not normalized. Which is ignored on COPY, as per docs:

It is important to understand that the tsvector type itself does not perform any word normalization; it assumes the words it is given are normalized appropriately for the application

If you run:

SELECT id, vector, relative_path, title
FROM site_server.indexed_url
WHERE to_tsvector(vector) @@ plainto_tsquery('english','booking') query;

It will produce expected result I think.

Upvotes: 1

Related Questions