Re-Compute a Table Column’s Default Value
Jim_Knicely
- Select Field - Administrator
You can specify a table column's default value using a DEFAULT expression. Vertica evaluates the DEFAULT expression and sets the column on load operations, if the operation omits a value for the column.
That DEFAULT expression can be derived from another column in the same table!
When you update the value in a base column, you will need to manually re-compute the value in your derived column (the one with the DEFAULT constraint), setting it equal to the keyword DEFAULT.
Example:
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;
OUTPUT
--------
2
(1 row)
dbadmin=> SELECT * FROM a;
b | c
----+---
5 | 2
10 | 4
(2 rows)
dbadmin=> UPDATE a SET c = DEFAULT;
OUTPUT
--------
2
(1 row)
dbadmin=> SELECT * FROM a;
b | c
----+----
5 | 10
10 | 20
(2 rows)
Helpful Links:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/column-constraint.htm
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Tables/ColumnManagement/ColumnDefaultValue.htm
Have fun!
0