How to use tsvector_update_trigger with jsonb column?

Question

I need to create cached ts_vector column from jsonb value. I try to create a trigger

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON tags FOR EACH ROW EXECUTE PROCEDURE
  tsvector_update_trigger(
    tsv, 'pg_catalog.english', "translations#>>'{en,name}'"
  );

But in a result I get an error

ERROR:  column "translations#>>'{en,name}'" does not exist

Answer 1

You don't. You will need to write your own trigger function. The built in one only works for text columns, not for jsonb columns and not for functions over columns.

On newer versions, an alternative would be use a generated column.

create table tags (
    translations jsonb, 
    tsv tsvector generated always as (to_tsvector('pg_catalog.english',translations#>>'{en,name}')) stored 
);

Answer 2

Double Quote are indicators for column, but postgres escapes single quotes by doubling it

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON tags FOR EACH ROW EXECUTE PROCEDURE
  tsvector_update_trigger(
    tsv, 'pg_catalog.english', 'translations#>>''{en,name}'''
  );