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:
- Queries (see Flattened Tables)
- Other columns in the same table
- Literals (constants)
- All operators supported by Vertica
-
The following categories of functions:
- Null-handling
- User-defined scalar
- System information
- String
- Mathematical
- Formatting
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)),
...