Define a Vertica Flattened Table Column as Having Both DEFAULT and SET USING Constraints

Jim_Knicely · January 2020

Columns in a flattened table can query other tables with constraints DEFAULT and SET USING .

Vertica executes DEFAULT queries only on new rows when they are added to the flattened table, through load operations such as INSERT and COPY. Thereafter, changes in the original data sources have no effect on the flattened table.

Vertica executes SET USING queries only when you invoke the function REFRESH_COLUMNS. Load operations set SET USING columns in new rows to NULL. After the load, you must call REFRESH_COLUMNS to populate these columns from the queried tables. This can be useful in two ways: you can defer the overhead of updating the flattened table to any time that is convenient; and you can repeatedly query source tables for new data.

A flattened table column is not limited to having either a DEFAULT or SET USING constraint. It can have both constriants!

Example:

dbadmin=> CREATE TABLE customer_dim (customer_id INT, customer_name VARCHAR(100));
CREATE TABLE

dbadmin=> INSERT INTO customer_dim SELECT 1, 'LUKE';
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT  NTO customer_dim SELECT 2, 'HAN';
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO customer_dim SELECT 3, 'LANDO';
 OUTPUT
--------
      1
(1 row)

dbadmin=> CREATE TABLE customer_fact (pk INT, customer_id INT, customer_name VARCHAR(100) DEFAULT USING (SELECT customer_name FROM customer_dim WHERE customer_id = customer_fact.customer_id));
CREATE TABLE

dbadmin=> INSERT INTO customer_fact (pk, customer_id) SELECT 1, 2;
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO customer_fact (pk, customer_id) SELECT 2, 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM customer_fact;
 pk | customer_id | customer_name
----+-------------+---------------
  1 |           2 | HAN
  2 |           1 | LUKE
(2 rows)


dbadmin=> UPDATE customer_dim SET customer_name = 'BEN' WHERE customer_id = 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT refresh_columns('customer_fact', 'customer_name', 'UPDATE'); COMMIT;
      refresh_columns
---------------------------
 refresh_columns completed
(1 row)

COMMIT

dbadmin=> SELECT * FROM customer_fact;
 pk | customer_id | customer_name
----+-------------+---------------
  1 |           2 | HAN
  2 |           1 | BEN
(2 rows)

Useful Links:
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AnalyzingData/FlattenedTables/FlattenedTables.htm
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AnalyzingData/FlattenedTables/SetUsingVersusDefault.htm

Have Fun!