Re-Compute a Table Column’s Default Value Immediately

Jim_Knicely · November 2018

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 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/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
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AnalyzingData/Views/CreatingViews.htm

Have fun!