Will changing PostgreSQL generated column logic affect previous records?

user_13650013 0
Viewed 35

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'
postgresql
1 Answers

Generated columns:

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.

user_70706130
Related