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. DEFAULT and SET USING constraints can be used for columns of all data types. Like other columns, you can set these constraints when you create the flattened table, or any time thereafter by modifying the table DDL:

• 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

In all cases, the expressions that you set for these constraints are stored in the system table COLUMNS, in columns COLUMN_DEFAULT and COLUMN_SET_USING.

Supported expressions

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

• Queries

• 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

For more information about DEFAULT and SET USING expressions, including restrictions, see Defining column values.