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?

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;