1

I have a fairly big PostgreSQL 13 table that I need to add a new TEXT column and with this new column I would like to add a GENERATED ALWAYS column with the following definition:

ALTER TABLE my_table
ADD COLUMN text_col TEXT,
ADD COLUMN text_col__tsvector GENERATED ALWAYS AS (to_tsvector('pt_unaccent', "text_col")) STORED;

However when I run the above DDL it takes a long time to complete. As I understand that is because the text_col__tsvector need to run the expression for all rows in the table even if the text_col is NULL.

My question is: knowing that the text_col column will always start as NULL (no default value is defined) is there a way to make postgres skip the first generation part in the creation of the text_col__tsvector? Or change the expression part (to_tsvector('pt_unaccent', "text_col")) of the GENERATED ALWAYS column to ignore NULL values?

1 Answer 1

1

Currently (postgresql 16) only stored generated columns are implemented and adding such a column always performs full table rewrite with the calculated and populated value. There is no way to avoid such table rewrite.

You can populate a field's value the old-fashioned way, using a trigger rather than a generated column. It is also possible that you do not need new column at all, but a index on expression. An index on expression can be built concurrently.

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.