REFRESH_COLUMNS

Refreshes table columns that are defined with the constraint SET USING. All refresh operations associated with a REFRESH_COLUMNS operation belong to the same transaction. All tables and columns specified by REFRESH_COLUMNS must be refreshed; otherwise, the entire operation is rolled back.

Syntax

REFRESH_COLUMNS ( 'tables', '[columns]' [, '[refresh‑mode]' ] )

Parameters

tables

A comma-delimited list that specifies the tables to refresh:

[[database.]schema.]table[,…]

Important: If you specify multiple tables, you must also set parameter refresh‑mode to REBUILD.

columns

Specifies one or more columns to refresh, as follows:

'' (empty string)
Refresh all SET USING/DEFAULT USING columns in the specified tables.
[[[database.]schema.]table.]column[,…]
Refresh all columns in the comma-delimited list. The following requirements apply:
- If REFRESH_COLUMNS specifies multiple tables, all column names must be qualified by their table names. If the target tables span multiple schemas, all column names must be fully qualified by their schema and table names.
- All specified columns must have a SET USING or DEFAULT USING constraint.
For example:
```
SELECT REFRESH_COLUMNS ('t1, t2', 't1.a, t2.b', 'REBUILD');
```
[[database.]schema.]table.*
Refresh all SET USING/DEFAULT USING columns in table. For example:
```
SELECT REFRESH_COLUMNS ('t1, t2', 't1.*, t2.b', 'REBUILD');
```

If you specify a database, it must be the current database.

refresh‑mode

Specifies how to refresh SET USING columns:

UPDATE: Marks original rows as deleted and replaces them with new rows. In order to save these updates, you must issue a COMMIT statement.
REBUILD: Replaces all data in the specified columns. The rebuild operation is auto-committed.

If set to an empty string or omitted, REFRESH_COLUMNS executes in UPDATE mode. If you specify multiple tables, you must explicitly specify REBUILD mode.

In both cases, REFRESH_COLUMNS returns an error if any SET_USING column is defined as a primary or unique key in a table that enforces those constraints.

See REBUILD Mode Restrictions for limitations on using the REBUILD option.

Privileges

MODIFY privilege on the target table, USAGE privilege on its schema
For each SET USING column to refresh that queries another table or view: SELECT privilege on the queried table/view, USAGE privilege on its schema

REBUILD versus REFRESH Modes

In general, UPDATE is a better choice when changes to SET USING column data are confined to a relatively small number of rows. Use REBUILD when a significant amount of SET USING column data is stale and must be updated. It is generally good practice to call REFRESH_COLUMNS with REBUILD on any new SET USING column—for example, to populate a SET USING column after adding it with ALTER TABLE…ADD COLUMN.

REBUILD Mode Restrictions

If you call REFRESH_COLUMNS on a SET USING column and specify the refresh mode as REBUILD, Vertica returns an error if any of the following conditions is true for that column:

Specified as a table partition key.
Included in a live aggregate projection or projection with expressions.
Included in a projection's sort order or segmentation.
Included in a projection, and the projection omits an anchor table column that is referenced in the column's SET USING expression.
Included in a projection's GROUPED clause.

Examples

See Flattened Table Example and SET USING versus DEFAULT in Analyzing Data.