2

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

2 Answers 2

4

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 
);
0

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}'''
  );
2
  • Same error Query 1 ERROR: ERROR: column "translations#>>'{en,name}'" does not exist
    – Molfar
    Mar 8, 2021 at 12:08
  • please show us the complete tags rtable and also the procedure, so that we can debug it better
    – nbk
    Mar 8, 2021 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.