Is it possible to add a GENERATED COLUMN to a large table without locking/downtime on postgres?

Question

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?

Answer 1

No, there is no way to do that.

Answer 2

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.
Related:

• Computed / calculated / virtual / derived columns in PostgreSQL
• Attribute notation for function call gives error