Modifying SET USING and DEFAULT columns

Modifying a SET USING and DEFAULT expression

ALTER TABLE...ALTER COLUMN can set an existing column to SET USING or DEFAULT, or change the query expression of an existing SET USING or DEFAULT column. In both cases, existing values remain unchanged. Vertica refreshes column values in the following cases:

• DEFAULT column: Refreshed only when you load new rows, or when you invoke UPDATE to set column values to DEFAULT.

• SET USING column: Refreshed only when you call REFRESH_COLUMNS on the table.

For example, you might set an entire column to NULL as follows:

=> ALTER TABLE orderFact ALTER COLUMN cust_name SET USING NULL;
ALTER TABLE
=> SELECT REFRESH_COLUMNS('orderFact', 'cust_name', 'REBUILD');
REFRESH_COLUMNS
---------------------------
refresh_columns completed
(1 row)

=> SELECT order_id, order_date::date, cid, cust_name, cust_gender, amount FROM orderFact ORDER BY cid;
  order_id | order_date | cid | cust_name | cust_gender | amount
 ----------+------------+-----+-----------+-------------+---------
    100 | 2018-12-31 |   1 |           | F           |   15.00
    200 | 2018-12-31 |   1 |           | F           | 1000.00
    300 | 2018-12-31 |   2 |           | M           |  -50.00
    500 | 2018-12-31 |   2 |           | M           |  200.00
    400 | 2018-12-31 |   3 |           | F           |  100.00
(5 rows)

For details, see Defining column values

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. For example:

=> ALTER TABLE orderFact ALTER COLUMN cust_name DROP SET USING;
ALTER TABLE

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 also includes the CASCADE option. 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 orderFact.cust_gender:

=> ALTER TABLE custDim DROP COLUMN gender;
ROLLBACK 7301:  Cannot drop column "gender" since it is referenced in the default expression of table "public.orderFact", column "cust_gender"

To drop this column, use the CASCADE option:

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

Vertica removes the DEFAULT constraint from orderFact.cust_gender as part of the drop operation. However, cust_gender retains the data that it previously queried from the dropped column custDim.gender:

=> SELECT EXPORT_TABLES('','orderFact');
                                                EXPORT_TABLES
------------------------------------------------------------------------------------------------------------
CREATE TABLE public.orderFact
(
    order_id int NOT NULL,
    order_date timestamp NOT NULL DEFAULT (now())::timestamptz(6),
    cid int,
    cust_name varchar(20),
    cust_gender varchar(1) SET USING NULL,
    amount numeric(12,2),
    CONSTRAINT C_PRIMARY PRIMARY KEY (order_id) DISABLED
)
PARTITION BY ((orderFact.order_date)::date) GROUP BY (CASE WHEN ("datediff"('year', (orderFact.order_date)::date, ((now())::timestamptz(6))::date) >= 2) THEN (date_trunc('year', (orderFact.order_date)::date))::date WHEN ("datediff"('month', (orderFact.order_date)::date, ((now())::timestamptz(6))::date) >= 2) THEN (date_trunc('month', (orderFact.order_date)::date))::date ELSE (orderFact.order_date)::date END);
ALTER TABLE public.orderFact ADD CONSTRAINT C_FOREIGN FOREIGN KEY (cid) references public.custDim (cid);

(1 row)

=> SELECT * FROM orderFact;
 order_id |         order_date         | cid | cust_name | cust_gender | amount
----------+----------------------------+-----+-----------+-------------+---------
      400 | 2021-01-05 13:27:56.026115 |   3 |           | F           |  100.00
      300 | 2021-01-05 13:27:56.026115 |   2 |           | F           |  -50.00
      200 | 2021-01-05 13:27:56.026115 |   1 |           | F           | 1000.00
      500 | 2021-01-05 13:30:18.138386 |   3 |           | F           |  750.00
      100 | 2021-01-05 13:27:56.026115 |   1 |           | F           |   15.00
      500 | 2021-01-05 13:27:56.026115 |   2 |           | F           |  200.00
(6 rows)