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! To see how, check out this quick tip!
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.
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;
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/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
Have fun!
About the Author
Phil Molea
Sr. Information Developer, Vertica
Phil developed technical documentation in the areas of security and diagnostics for the Vertica Analytics Platform that enabled companies to extract value from their data at the speed and scale they need to thrive in today’s economy.
(Sadly, Phil passed away recently. He will be missed.)
