I am trying to add a new column as a generated column to a large table (over millions of rows). This generated column takes in the id column and performs a custom PostgreSQL function and returns it. I think generated columns require a rewrite of the whole table, so it does not matter if you create the column as NULL/NOT NULL. I have thought have trying to create a new nullable column with a default and then try to add the generated as always expression, but this would require dropping the whole column and recreate it as a generated column. Is there any way to do this without locking the whole table?

2 Answers 2


No, there is no way to do that.


Not possible with the currently implemented STORED generated columns. It would probably work for VIRTUAL generated columns, which are not implemented, yet (as of pg 15).

Depending on your undisclosed use case, a VIEW, MATERIALIZED VIEW, or an expression index created CONCURRENTLY might do the job.

Your Answer

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

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