SQL update column used by generated column-CodePudding

In Postgresql 14, I have:

CREATE TABLE items
(
    id bigint NOT NULL PRIMARY KEY,
    name varchar(40) not null,
    name_search tsvector GENERATED ALWAYS AS (to_tsvector('simple', name)) STORED
);

Now I would like to update the table, to make the "name" column of a larger size:

ALTER TABLE items 
    ALTER COLUMN name TYPE varchar(50);

But I get an error

SQL Error [42601]: ERROR: cannot alter type of a column used by a generated column

Detail: Column "name" is used by generated column "name_search".

I could not find on the internet any workarounds or fixes for this problem. How to solve it? Is it possible to update the column type without drop of the old one and a creation of a temp table?

My thinking is - the old and new column value types should be compatible. I am making the field larger so it could store more data therefore it should be possible to expand the existing column. Or not?

CodePudding user response：

you must drop the generated column, alter the table and readd the generated column:

set lock_timeout = '1s';
begin
alter table items drop column name_search ;
ALTER TABLE items ALTER COLUMN name TYPE varchar(50);
alter table items add column name_search tsvector GENERATED ALWAYS AS (to_tsvector('simple', name)) STORED ;
commit