2

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?

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
1

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.