
When you update the value in a base column, you will need to re-compute the value in your derived column (the one with the DEFAULT constraint).
You won’t be able to update both the base column and derived column at the same time!
Examples:
dbadmin=> CREATE TABLE A (b INT, c INT DEFAULT b * 2);
CREATE TABLE
dbadmin=> INSERT INTO A (b) SELECT 1;
OUTPUT
--------
1
(1 row)
dbadmin=> INSERT INTO A (b) SELECT 2;
OUTPUT
--------
1
(1 row)
dbadmin=> SELECT * FROM a;
b | c
---+---
1 | 2
2 | 4
(2 rows)
dbadmin=> UPDATE a SET b = b * 5, c = DEFAULT;
OUTPUT
--------
2
(1 row)
dbadmin=> SELECT * FROM a;
b | c
----+---
5 | 2
10 | 4
(2 rows)
It appears that column “C” was not re-computed. But it was. When Vertica updates the DEFAULT value it is looking at the “current” value of the base column, not the “new” value.
If you want to automatically have a derived column updated, a better solution might be a database view!
dbadmin=> CREATE VIEW a_view AS SELECT b, b * 2 AS c FROM a;
CREATE VIEW
dbadmin=> SELECT * FROM a_view;
b | c
----+----
5 | 10
10 | 20
(2 rows)
Helpful Links:https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Statements/column-constraint.htm
https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/AdministratorsGuide/Tables/ColumnManagement/ColumnDefaultValue.htm
https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/AnalyzingData/Views/CreatingViews.htm
Have fun!