Re-Compute a Table Column’s Default Value: Quick Tip

Posted October 31, 2018 by Phil Molea, Sr. Information Developer, Vertica

City in Blur Motion
Jim Knicely authored this tip.

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!