Re-Compute a Table Column’s Default Value

Jim_Knicely

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!