I've found a strange example where a generated column doesn't seem to update when I would expect it to.
create table example (
"id" bigserial primary key,
"value" text
);
insert into example ("value") values
('A'),
('B'),
('C');
alter table example add column "vector" tsvector generated always as (to_tsvector('english', coalesce("value", ''))) stored;
insert into example ("value") values
('some'),
('thing');
I would expect all the vector columns to be updated and contain a vector of the contents of the value column, but some are and some aren't. Even running an update doesn't fill the column. It basically looks like:
| id | value | vector |
|---|---|---|
| 1 | A | |
| 2 | B | 'b':1 |
| 3 | C | 'c':1 |
| 4 | some | |
| 5 | thing | 'thing':1 |
The strangest thing is I have tried dropping the column, then re-adding it and I get the exact same results. I've also had times where updating the value column generates the vector, but when I set the value column back to the previous value the vector is blank again.
What am I missing here?
