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

Question

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   |   description   |
1  |   skis    |     my skis     |
2  |   bike    |                 | 

I am creating the document with:

SELECT title, description,
setweight(to_tsvector(title), 'A'::"char") ||
setweight(to_tsvector(description), 'C'::"char")     
AS document
FROM inventory

The result I expected to see was:

title |  description  |  document  |
skis  |    my skis    |'ski':1A,3C |
bike  |               | 'bike':1A  | 

but what I actually got was:

title |  description  |  document  |
skis  |    my skis    |'ski':1A,3C |
bike  |               |            |

This seems like a bug. Adding in any single letter or number or anything to description makes it so the document comes up correctly, but a null value in a single column cause the entire document to be empty. Why should a description be required to be able to search on title and description? Am I misunderstanding something?

Burnie

Answer 1

It seems that this is a standard behaviour of SQL.

As a workaround you can use the function COALESCE in the query:

SELECT title, description,
setweight(to_tsvector(title), 'A'::"char") ||
setweight(to_tsvector(coalesce(description, '')), 'C'::"char")     
AS document
FROM inventory;

Zamia

I tried this using the COALESCE function and it still did not work. I think that COALESCE only works to get non-null values for JOINed values with a one-to-many relationship. If it doesn't find a non-null value, it still breaks – Burnie

I think I figured it out. I was not adding the empty string to the COALESCE function: ie. coalesce(string_agg(tags, ' '), ''). Therefore when only NULL values of tags existed, it was still returning NULL. Adding the empty string fixed the problem. – Burnie

Answer 2

It turns out that it is the NULL value specifically that causes this issue. I was able to get around it by setting all of the columns which would be included in the search index to NOT NULL and DEFAULT "". If anyone has an explanation as to why it functions the way it does, I would love to know.

Burnie