Creating Flattened Tables

A flattened table is typically a fact table where one or more columns query other tables for their values, through DEFAULT or SET USING constraints. Like other columns, you can set these constraints when you create the flattened table, or any time thereafter by modifying the table schema:

CREATE TABLE...(..., column-name data-type { DEFAULT | SET USING } expression,...)
ALTER TABLE...ADD COLUMN column-name { DEFAULT | SET USING } expression
ALTER TABLE...ALTER COLUMN column-name { SET DEFAULT | SET USING } expression

DEFAULT and SET USING constraints can be used for columns of all data types. The expressions that you set for these constraints are stored in the system table COLUMNS, in columns COLUMN_DEFAULT and COLUMN_SET_USING. Both constraints support the same expressions. For detailed information, including restrictions, see Defining Column Values in the Administrator's Guide.

Supported Expressions

DEFAULT and SET USING generally support the same expressions. These include:

Disambiguating Predicate Columns

If a SET USING or DEFAULT query expression joins two columns with the same name, the column names must include their table names. Otherwise, Vertica assumes that both columns reference the dimension table, and the predicate always evaluates to true.

For example, tables orderFact and custDim both include column cid. Flattened table orderFact defines column cust_name with a SET USING query expression. Because the query predicate references columns cid from both tables, the column names are fully qualified:

CREATE TABLE public.orderFact
(
    ...
    cid int REFERENCES public.custDim(cid),
    cust_name varchar(20) SET USING ( 
       SELECT name FROM public.custDim WHERE (custDIM.cid = orderFact.cid)),
    ...