CODEWITHSUNDEEP
postgresqlsearchnullfull-text-searchtsvector
knsheely
knsheely

Reputation: 623

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   |   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?

Upvotes: 8

Views: 4208

Answers (2)

Artur
Artur

Reputation: 688

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;

Upvotes: 10

knsheely
knsheely

Reputation: 623

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.

Upvotes: 0

Related Questions