I am creating a search inside a Rails application using pg_search gem. However, one of the tables has a field of type Textdatatype, in which the content is slightly larger than usual.
Now that I need to adjust tsvector columnfor the columns Text, I come across some restrictions that are related to the size of the text field and the size of the tsvector.
ERROR: string is too long for tsvector (5068741 bytes, max 1048575 bytes)
Is there a way to define a condition for skipping large fields Textwhen creating a tsvector column in a trigger SQLto do something like this:
pseudo code:
execute(<<-TRIGGERSQL)
CREATE OR REPLACE FUNCTION public.essays_before_insert_update_row_tr()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
If (SELECT LEN(body_text) FROM essays) <= 1048575
new.tsv_body_text := to_tsvector('pg_catalog.english', coalesce(new.body_text,''));
RETURN NEW;
End
END;
$function$
TRIGGERSQL
execute("CREATE TRIGGER essays_before_insert_update_row_tr BEFORE INSERT OR UPDATE ON \"essays\" FOR EACH ROW EXECUTE PROCEDURE essays_before_insert_update_row_tr()")
which I found unanswered:
Postgresql - convert text to ts_vector
source
share