Stack Overflow
Stack Overflow

Questions tagged [tsvector]

141 questions
21
votes
1 answer

"to_tsquery" on tsvector yields different results when using "simple" and "english"?

I've been enlisted to help on a project and I'm diving back into PostgreSQL after not working with it for several years. Lack of use aside, I've never run into using tsvector fields before and now find myself facing a bug based on them. I read the…
phatskat
  • 1,797
  • 1
  • 15
  • 32
18
votes
1 answer

Postgres full text search across multiple related tables

This may be a very simplistic question, so apologies in advance, but I am very new to database usage. I'd like to have Postgres run its full text search across multiple joined tables. Imagine something like a model User, with related models…
William Jones
  • 18,089
  • 17
  • 63
  • 98
16
votes
1 answer

Should I store empty tsvector values or NULL values?

When storing a tsvector value in a column, for records with no search terms, should I store an empty tsvector or a NULL value? Does it matter? Is there any difference in terms of performance, or storage overhead from storing empty vectors? In other…
mindplay.dk
  • 7,085
  • 3
  • 44
  • 54
15
votes
1 answer

Postgres full text search: Multiple columns, cross table

I am new to Postgres and came across full text search feature. I want to achieve the following: Specify some table and fields to search on. When the user search for some text, it should be searched on above specified table fields. e.g. CREATE…
Damandeep Thakur
  • 165
  • 1
  • 1
  • 7
14
votes
2 answers

Apply postgreSQL trigger to existing rows in database

I am using PostgeSQL 9.2.2. My database schema is pg_rocks_post title | character varying(1024) | not null body | text | not null body_title_tsv | tsvector …
harijay
  • 11,303
  • 12
  • 38
  • 52
13
votes
2 answers

Rails migrations with database-specific data types

I'm currently running a Rails migration where I am adding a datatype specific to Postgres, the tsvector. It holds search information in the form that Postgres expects for its built-in text searching capabilities. This is the line from my…
William Jones
  • 18,089
  • 17
  • 63
  • 98
12
votes
4 answers

Match a phrase starting with a prefix with full text search

I'm looking for a way to emulate something like SELECT * FROM table WHERE attr LIKE '%text%' using a tsvector in PostgreSQL. I've created a tsvector attribute without using a dictionary. Now, a query like ... SELECT title FROM table WHERE title_tsv…
itsame69
  • 1,540
  • 5
  • 17
  • 37
12
votes
1 answer

Ruby on Rails: How to sanitize a string for SQL when not using find?

I'm trying to sanitize a string that involves user input without having to resort to manually crafting my own possibly buggy regex if possible, however, if that is the only way I would also appreciate if anyone can point me in the right direction to…
William Jones
  • 18,089
  • 17
  • 63
  • 98
11
votes
1 answer

PostgreSQL: How to go around ts_vector size limitations?

I'm creating a search inside a Rails app using the pg_search gem. However, one of the tables have a Text datatype field that it's content happens to be a little larger than usual. Now when I need to setup a tsvector column for the text columns, I…
0bserver07
  • 3,390
  • 1
  • 28
  • 56
10
votes
1 answer

Postgres full text search on array column, with index

Using Postgres, I want to perform a full text search that includes an array column, using an index. Let's start with a hypothetical schema: CREATE TABLE book (title TEXT, tags TEXT[]); -- tags are lowercase a-z, dashes, and $ We want a query that…
stickfigure
  • 13,458
  • 5
  • 34
  • 50
10
votes
2 answers

Concat two postgresql tsvector fields originating in separate tables into single postgresql view to enable joined full text search

I have a postgresql view that is comprised as a combination of 3 tables: create view search_view as select u.first_name, u.last_name, a.notes, a.summary, a.search_index from user as u, assessor as a, connector as c where a.connector_id = c.id and…
David Watson
  • 3,394
  • 2
  • 36
  • 51
9
votes
1 answer

Fulltext search combined with fuzzysearch in PostgreSQL

I want to realize a fulltext search in postgresql combined with a kind of fuzzy search. For my testarea I followed up this article: https://blog.lateral.io/2015/05/full-text-search-in-milliseconds-with-postgresql/ and everything is working fine. But…
Chris
  • 121
  • 1
  • 9
8
votes
1 answer

"invalid input syntax for type numeric" for entering "emptyness"

I have a table with three columns using the NUMERIC type. However, two of them have nothing (ie. empty). Here it's the code: CREATE TABLE profiles( ID SMALLINT, FID SMALLINT, SURVEY VARCHAR(100), PROFILE VARCHAR(100), TYPE VARCHAR(100), SOURCE…
Gery
  • 8,390
  • 3
  • 22
  • 39
7
votes
1 answer

Is there any reason to include a `tsvector` column in a postgres table rather than in the index?

I have a table with about 100 million rows and a text field that I'd like to search over. I've come up with two methods for doing this and I'd like to know the performance implications of each method. Method 1: This is the method recommended by…
Mike Izbicki
  • 6,286
  • 1
  • 23
  • 53
7
votes
2 answers

to_tsvector is empty if any column has no data in PostgreSQL Full Text Search

I am trying to implement a Postgre SQL Full Text Search but I am running into a problem with the entire document returning empty if any of the columns set with to_tsvector are empty. I have a table that looks like the following: id | title | …
knsheely
  • 543
  • 5
  • 13
1
2 3
…
9 10