Search code examples
postgresqlpostgresql-12

Will adding new generated column lock table in PostgreSQL 12

It is not clear from PostgreSQL documentation: will adding new generated column lock table?

I have big table in my PostgreSQL 12 installation and I need to execute SQL-patch:

ALTER TABLE abc ADD COLUMN gen TEXT GENERATED AS ALWAYS (...) STORED;

So, in my case it is unacceptable to lock whole table for long time. Is it safe to add new generated column?

Solution

  • Since the generated column is STORED, PostgreSQL has to rewrite the table, so yes, this will take a long time.

    Why don't you use a view to calculate the column on SELECT?