Derive a Table Column’s Default Value from another Column

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

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!

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)

Helpful Link:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/column-constraint.htm

Have fun!

Sign In or Register to comment.