Let's say we have a table that includes a generated column that concatenates the string of two columns (data_a data_b):
| id | data_a | data_b | generated_data |
|---|---|---|---|
| 1 | abc | 123 | '123abc' |
| 2 | xyz | 890 | 'xyz890' |
... but we want to change the generation logic, for example reversing the concatenation order (data_b data_a). Would that "backfill" my previous records, or maintain them but only update the new records?
IE, would this change result in this ("backfill"):
| id | data_a | data_b | generated_data |
|---|---|---|---|
| 1 | abc | 123 | 'abc123' |
| 2 | xyz | 890 | '890xyz' |
| 3 | lmn | 567 | '567lmn' |
... or this ("maintain")?
| id | data_a | data_b | generated_data |
|---|---|---|---|
| 1 | abc | 123 | '123abc' |
| 2 | xyz | 890 | 'xyz890' |
| 3 | lmn | 567 | '567lmn' |
CodePudding user response：
A stored generated column is computed when it is written (inserted or updated) and occupies storage as if it were a normal column
PostgreSQL currently implements only stored generated columns.
If you could change the expression in place it would not backfill them. You would have to make an explicit UPDATE to make that happen.
The bigger issue is that I can't see anyway to alter the generation code without dropping the column and adding it back with the new expression. Doing so though will change all the column values to the new result of the new expression.