Search code examples
postgresqlcoalescepostgresql-9.5

PostgreSQL 9.5 Coalesce

I have a table "recipes" with columns named "name", "description", "preparation". To enable Full-Text-Search i did:

ALTER TABLE recipes ADD COLUMN recipes_searchtext TSVECTOR;

CREATE INDEX idx_recipes_searchtext_gin ON recipes USING GIN(recipes_searchtext);
UPDATE recipes SET recipes_searchtext = 
setweight(to_tsvector('german',name), 'A') ||
setweight(to_tsvector('german',description), 'B') ||
setweight(to_tsvector('german',preparation), 'C');

I'm new to PostgreSQL, but as far as i can see after some testing this works fine for me.

But then i read about "COALESCE" to handle NULL Values. So i tried:

UPDATE recipes SET recipes_searchtext = 
setweight(to_tsvector('german',COALESCE(name), 'A')) ||
setweight(to_tsvector('german',COALESCE(description), 'B')) ||
setweight(to_tsvector('german',COALESCE(preparation), 'C'));

what resulted in Error Message

Funktion to_tsvector(unknown, character varying, unknown

Can someone please give me a hint what i'm doing wrong?

regards dirk

Solution

  • Looks like you have the parentheses in the wrong place.

    to_tsvector takes at most 2 arguments, but the way you have it right now it is taking three.

    This should fix it:

    UPDATE recipes SET recipes_searchtext = 
setweight(to_tsvector('german',COALESCE(name)), 'A') ||
setweight(to_tsvector('german',COALESCE(description)), 'B') ||
setweight(to_tsvector('german',COALESCE(preparation)), 'C');

    However, I don't see a difference between your queries. coalesce will return null if all its arguments are null. You can provide a default value if you want like so: coalesce(name, 'Nothing') which will return 'Nothing' if name is null