Libre base doesnt seem to support full text search (FTS) operators, can anyone help please?

libre base supports a memo [tsvector] column type so I figure qry should be able to search this type.
the syntax i use successfully on the linked postgreql db to do this
select id, name, ingredients, method,document_with_weights
from headers
where document_with_weights @@ plainto_tsquery(‘turmeric’)
fails with no meaningful error message from BASE query

document_with_weights is the tsvector column. I can find no reference in BASE documentation to memo [tsvector] column type or anything relevant to my problem ,has anyone tried to do FTS search from base query please?


You have a bit of misunderstanding regarding Base. Field types shown in Base are only shown because of the database you have chosen. If you were using the HSQLDB embedded database you would not find type memo [tsvector]. Thisis the main reason you do not find a reference in the Base documentation. The documentation does cover some on fields especially on the embedded databases. In general, however, it is best to refer to the documentation of the database you are using.

Had not seen or used this field type until a short time ago. It does seem to work with Base but there are certainly items I do not fully comprehend yet. Here is what worked for me.

Set up what I thought was similar to your question:

image description

Entered some data:

Note that I am still learning the construct of a ‘tsvector’ field and some of the converted text is truncated. This does not affect the testing.

Then here is the SQL result you presented in the question:

Here are the liked pages I used to come up with this:


Just noticed my sample did not use plainto_tsquery so here is a test with that (also added weights during my testing):

Here is how I have been updating the tsvector field:

UPDATE headers SET 
    document_with_weights = setweight(to_tsvector(name), 'A')  ||
        setweight(to_tsvector(ingredients),'B') ||

If this answers your question please tick the :heavy_check_mark: (upper left area of answer). It helps others to know there was an accepted answer.

Should also mention, to run this query you must turn on Run SQL command directly from the query toolbar icon or from the menu Edit->Run SQL command directly.

I believe the truncated entries I had mentioned is due to the dictionary used. It will only use the necessary number of characters to get correct result.

thank you very much for all this effort. you are right my difficulties were caused by thinking i had to satisfy BASE whereas i just had to pass the sql directly to postgres.BASE linked to postgresql is a functional option to deliver this. Thanks again graham.