Modifying SET USING and DEFAULT Columns

Removing SET USING and DEFAULT Constraints

You remove a column's SET USING or DEFAULT constraint with ALTER TABLE…ALTER COLUMN, as follows:

ALTER TABLE table-name ALTER COLUMN column-name DROP { SET USING | DEFAULT };

Vertica removes the constraint from the specified column, but the column and its data are otherwise unaffected.

Modifying a SET USING and DEFAULT Expression

You can change the query expression of an existing SET USING or DEFAULT column by calling ALTER TABLE…ALTER COLUMN. When you modify a SET USING column expression, Vertica automatically invokes REFRESH_COLUMNS using UPDATE mode, and repopulates that column. If a large number of rows must be refreshed, the following approach might be preferable:

1. Drop the column with ALTER TABLE…DROP COLUMN.
2. Call ALTER TABLE…ADD COLUMN to add a new one with the desired SET USING expression.

This approach has two benefits:

• Avoids the immediate call to REFRESH_COLUMNS, and defers the overhead that is otherwise incurred.
• Lets you specify REBUILD mode when you call REFRESH_COLUMNS, which in many cases repopulates the column more efficiently.

Dropping Columns Queried by SET USING or DEFAULT

Vertica enforces dependencies between a flattened table and the tables that it queries. Attempts to drop a queried column or its table return an error unless the drop operation, such as DROP TABLE, also includes CASCADE. Vertica implements CASCADE by removing the SET USING or DEFAULT constraint from the flattened table. The table column and its data are otherwise unaffected.

For example, attempts to drop column name in table custDim returns a rollback error, as this column is referenced by SET USING column cust_name in orderFact:

=> ALTER TABLE custDim DROP COLUMN name;
ROLLBACK 7302:  Cannot drop column "name" since it is referenced in the set using expression of table "public.orderFact", column "cust_name"

To drop this column, use the CASCADE option:

=> ALTER TABLE custDim DROP COLUMN name CASCADE;
ALTER TABLE

Vertica removes the SET USING constraint from orderFact.cust_name as part of the drop operation. However, cust_name retains the data that it derived from dropped column custDim.name:

=> SELECT EXPORT_TABLES('','orderFact');
                                                EXPORT_TABLES
------------------------------------------------------------------------------------------------------------
CREATE TABLE public.orderFact
(
    order_id int NOT NULL,
    cid int,
    cust_name varchar(20)
    cust_gender varchar(1) DEFAULT ( SELECT custDim.gender
 FROM public.custDim
 WHERE (custDim.cid = orderFact.cid)),
    amount numeric(12,2)
);
ALTER TABLE public.orderFact ADD CONSTRAINT C_PRIMARY PRIMARY KEY (order_id) ENABLED;
ALTER TABLE public.orderFact ADD CONSTRAINT C_FOREIGN FOREIGN KEY (cid) references public.custDim (cid);
(1 row)

=> SELECT * FROM orderFact;
 order_id | cid | cust_name | cust_gender | amount
----------+-----+-----------+-------------+---------
      300 |   2 | Roz       | F           |  -50.00
      400 |   3 | Eva       | F           |  100.00
      100 |   1 | Alice     | F           |   15.00
      500 |   2 | Roz       | F           |  200.00
      200 |   1 | Alice     | F           | 1000.00
(5 rows)