ALTER statements

• 1: ALTER ACCESS POLICY
• 2: ALTER AUTHENTICATION
• 3: ALTER CA BUNDLE
• 4: ALTER DATA LOADER
• 5: ALTER DATABASE
• 6: ALTER FAULT GROUP
• 7: ALTER FUNCTION statements
• 7.1: ALTER AGGREGATE FUNCTION
• 7.2: ALTER ANALYTIC FUNCTION
• 7.3: ALTER FILTER
• 7.4: ALTER FUNCTION (scalar)
• 7.5: ALTER FUNCTION (SQL)
• 7.6: ALTER PARSER
• 7.7: ALTER SOURCE
• 7.8: ALTER TRANSFORM FUNCTION
• 8: ALTER HCATALOG SCHEMA
• 9: ALTER LIBRARY
• 10: ALTER LOAD BALANCE GROUP
• 11: ALTER MODEL
• 12: ALTER NETWORK ADDRESS
• 13: ALTER NETWORK INTERFACE
• 14: ALTER NODE
• 15: ALTER NOTIFIER
• 16: ALTER PROCEDURE (stored)
• 17: ALTER PROFILE
• 18: ALTER PROFILE RENAME
• 19: ALTER PROJECTION
• 20: ALTER RESOURCE POOL
• 21: ALTER ROLE
• 22: ALTER ROUTING RULE
• 23: ALTER SCHEDULE
• 24: ALTER SCHEMA
• 25: ALTER SEQUENCE
• 26: ALTER SESSION
• 27: ALTER SUBCLUSTER
• 28: ALTER SUBNET
• 29: ALTER TABLE
• 29.1: Projection column encoding
• 30: ALTER TLS CONFIGURATION
• 31: ALTER TRIGGER
• 32: ALTER USER
• 33: ALTER VIEW

1 - ALTER ACCESS POLICY

Performs one of the following actions on existing access policies:

• Modify an access policy by changing its expression, and by enabling/disabling the policy.

• Copy an access policy from one table to another.

Syntax

Modify policy:

ALTER ACCESS POLICY ON [[database.]schema.]table
   { FOR COLUMN column [ expression ] | FOR ROWS [ WHERE expression ] } { GRANT TRUSTED } { ENABLE | DISABLE }

Copy policy:

ALTER ACCESS POLICY ON [[database.]schema.]table
   { FOR COLUMN column | FOR ROWS } COPY TO TABLE table;

Parameters

`[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

table

The name of the table that contains the access policy you want to enable, disable, or copy.

FOR COLUMN column [expression]

Replaces the access policy expression that was previously set for this column. Omit expression from the FOR COLUMN clause in order to enable or disable this policy only, or copy it to another table.

FOR ROWS [WHERE expression]

Replaces the row access policy expression that was previously set for this table. Omit WHERE expression from the FOR ROWS clause in order to enable or disable this policy only, or copy it to another table.

GRANT TRUSTED

Specifies that GRANT statements take precedence over the access policy in determining whether users can perform DML operations on the target table. If omitted, users can only modify table data if the access policy allows them to see the stored data in its original, unaltered state. For more information, see Access policies and DML operations.

Important

GRANT TRUSTED only affects DML operations and does not enable users to see data that the access policy would otherwise mask. Specifying this option may allow users with certain grants to update data that they cannot see.

ENABLE | DISABLE

Indicates whether to enable or disable the access policy at the table level.

COPY TO TABLE tablename

Copies the existing access policy to the specified table. The copied access policy includes its enabled/disabled and GRANT TRUSTED statuses.

The following requirements apply:

• Copying a column access policy:

  • The target table must have a column of the same name and compatible data type.

  • The target colum must not have an access policy.

• Copying a row access policy: The target table must not have an access policy.

Privileges

Modify access policy

Non-superuser: Ownership of the table

Copy access policy

Non-superuser: Ownership of the source and destination tables

Examples

See Managing access policies

See also

2 - ALTER AUTHENTICATION

Modifies the settings for a specified authentication method.

Syntax

ALTER AUTHENTICATION auth_record {
   | { ENABLE | DISABLE }
   | { LOCAL | HOST [ { TLS | NO TLS } ] host_ip_address }
   | RENAME TO new_auth_record_name
   | METHOD value
   | SET param=value[,...]
   | PRIORITY value
   | [ [ NO ] FALLTHROUGH ]
}

Parameters

Privileges

Superuser

Examples

Enabling and Disabling Authentication Methods

This example uses ALTER AUTHENTICATION to disable the v_ldap authentication method and then enable it again:

=> ALTER AUTHENTICATION v_ldap DISABLE;
=> ALTER AUTHENTICATION v_ldap ENABLE;

Renaming Authentication Methods

This example renames the v_kerberos authentication method to K5. All users who have been granted the v_kerberos authentication method now have the K5 method granted instead.

=> ALTER AUTHENTICATION v_kerberos RENAME TO K5;

Modifying Authentication Parameters

This example sets the system user for ident1 authentication to user1:

=> CREATE AUTHENTICATION ident1 METHOD 'ident' LOCAL;
=> ALTER AUTHENTICATION ident1 SET system_users='user1';

When you set or modify LDAP or Ident parameters using ALTER AUTHENTICATION, Vertica validates them.

This example changes the IP address and specifies the parameters for an LDAP authentication method named Ldap1. Specify the bind parameters for the LDAP server. Vertica connects to the LDAP server, which authenticates the database client. If authentication succeeds, Vertica authenticates any users who have been associated with (granted) the Ldap1 authentication method on the designated LDAP server:

=> CREATE AUTHENTICATION Ldap1 METHOD 'ldap' HOST '172.16.65.196';

=> ALTER AUTHENTICATION Ldap1 SET host='ldap://172.16.65.177',
   binddn_prefix='cn=', binddn_suffix=',dc=qa_domain,dc=com';

The next example specifies the parameters for an LDAP authentication method named Ldap2. Specify the LDAP search and bind parameters. Sometimes, Vertica does not have enough information to create the distinguished name (DN) for a user attempting to authenticate. In such cases, you must specify to use LDAP search and bind:

=> CREATE AUTHENTICATION Ldap2 METHOD 'ldap' HOST '172.16.65.196';
=> ALTER AUTHENTICATION Ldap2 SET basedn='dc=qa_domain,dc=com',
   binddn='cn=Manager,dc=qa_domain,
   dc=com',search_attribute='cn',bind_password='secret';

Changing the Authentication Method

This example changes the localpwd authentication from hash to trust:

=> CREATE AUTHENTICATION localpwd METHOD 'hash' LOCAL;
=> ALTER AUTHENTICATION localpwd METHOD 'trust';

Set Multiple Realms

This example sets another realm for the authentication method krb_local:

=> ALTER AUTHENTICATION krb_local set realm = 'COMPANY.COM';

See also

• CREATE AUTHENTICATION
• DROP AUTHENTICATION
• GRANT (authentication)
• REVOKE (authentication)
• CLIENT_AUTH

3 - ALTER CA BUNDLE

Adds and removes certificates from or changes the owner of a certificate authority (CA) bundle.

Syntax

ALTER CA BUNDLE name
        [ADD CERTIFICATES ca_cert[, ca_cert[, ...]]
        [REMOVE CERTIFICATES ca_cert[, ca_cert[, ...]]
        [OWNER TO user]

Parameters

name

The name of the CA bundle.

ca_cert

The name of the CA certificate to add or remove from the bundle.

user

The name of a database user.

Privileges

Ownership of the CA bundle.

Examples

See Managing CA bundles.

See also

• CREATE CA BUNDLE
• DROP CA BUNDLE

4 - ALTER DATA LOADER

Changes the properties of a data loader created with CREATE DATA LOADER.

Syntax

ALTER DATA LOADER [schema.]name {
   SET TO copy-statement 
 | RETRY LIMIT { NONE | DEFAULT | limit }
 | RETENTION INTERVAL monitoring-retention
 | RENAME TO new-name 
 | OWNER TO user
}

Arguments

schema

Schema containing the data loader. The default schema is public.

name

Name of the data loader to alter.

SET TO copy-statement

The new COPY statement that the loader executes. The FROM clause typically uses a glob.

RETRY LIMIT { NONE | DEFAULT | limit }

Maximum number of times to retry a failing file. Each time the data loader is executed, it attempts to load all files that have not yet been successfully loaded, up to this per-file limit. If set to DEFAULT, at load time the loader uses the value of the DataLoaderDefaultRetryLimit configuration parameter.

Default: DEFAULT

RETENTION INTERVAL monitoring-retention

How long to keep records in the events table. DATA_LOADER_EVENTS records events for all data loaders, but each data loader has its own retention interval.

Default: 14 days

RENAME TO new-name

New name for the data loader.

OWNER TO user

New owner for the data loader.

Privileges

Non-superuser:

• USAGE on the schema.
• Owner or ALTER privilege on the data loader.

See also

• Automatic load
• CREATE DATA LOADER
• EXECUTE DATA LOADER
• DROP DATA LOADER

5 - ALTER DATABASE

Use ALTER DATABASE to perform the following tasks:

• Drop all fault groups and their child fault groups from a database.

• Restore down nodes, and revert active standby nodes to standby status.

• Specify the subnet name of a public network to use for import/export.

• Set and clear database configuration parameters.

To see the current value of a parameter, query system table CONFIGURATION_PARAMETERS or use SHOW DATABASE.

Syntax

ALTER DATABASE db-spec {
      DROP ALL FAULT GROUP
      | EXPORT ON { subnet-name | DEFAULT }
      | RESET STANDBY
      | SET [PARAMETER] parameter=value [,...]
      | CLEAR [PARAMETER] parameter[,...]
}

Parameters

db-spec

Specifies the database to alter, one of the following:

• The database name

• DEFAULT: The current database

DROP ALL FAULT GROUP

Drops all fault groups defined on the specified database.

EXPORT ON

Specifies the network to use for importing and exporting data, one of the following:

• subnet-name: A subnet of the public network.

• DEFAULT: Specifies to use a private network.

For details, see Identify the database or nodes used for import/export, and Changing node export addresses.

RESET STANDBY

Enterprise Mode only, restores all down nodes and reverts their replacement nodes to standby status. If any replaced nodes cannot resume activity, Vertica leaves their standby nodes in place.

SET [PARAMETER]

Sets the specified parameters.

CLEAR [PARAMETER]

Resets the specified parameters to their default values.

Privileges

Superuser

6 - ALTER FAULT GROUP

Modifies an existing fault group. ALTER FAULT GROUP can perform the following tasks:

• Add a node to or drop a node from an existing fault group.

• Add a child fault group to or drop a child fault group from a parent fault group.

• Rename a fault group.

Syntax

ALTER FAULT GROUP fault-group-name {
    | ADD NODE node-name
    | DROP NODE node-name
    | ADD FAULT GROUP child-fault-group-name
    | DROP FAULT GROUP child-fault-group-name
    | RENAME TO new-fault-group-name }

Parameters

fault-group-name

The existing fault group name you want to modify.

Tip

For a list of all fault groups defined in the cluster, query the FAULT_GROUPS system table.

node-name

The node name you want to add to or drop from the existing (parent) fault group.

child-fault-group-name

The name of the child fault group you want to add to or remove from an existing parent fault group.

new-fault-group-name

The new name for the fault group you want to rename.

Privileges

Superuser

Examples

This example shows how to rename the parent0 fault group to parent100:

=> ALTER FAULT GROUP parent0 RENAME TO parent100;
ALTER FAULT GROUP

Verify the change by querying the FAULT_GROUPS system table:

=> SELECT member_name FROM fault_groups;
   member_name
----------------------
v_exampledb_node0003
parent100
mygroup
(3 rows)

See also

• CREATE FAULT GROUP
• FAULT_GROUPS
• CLUSTER_LAYOUT
• Fault groups
• High availability with fault groups

7 - ALTER FUNCTION statements

Vertica provides ALTER statements for each type of user-defined extension. Each ALTER statement modifies the metadata of a user-defined function in the Vertica catalog:

Vertica also provides ALTER FUNCTION (SQL), which modifies the metadata of a user-defined SQL function.

7.1 - ALTER AGGREGATE FUNCTION

Alters a user-defined aggregate function.

Syntax

ALTER AGGREGATE FUNCTION [[db-name.]schema.]function-name( [ parameter-list ] ) {
    OWNER TO new-owner
    | RENAME TO new-name
    | SET SCHEMA new-schema
}

Parameters

[db-name.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function-name``

Name of the SQL function to alter.

arg-list

Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.

Note

Vertica supports function overloading, and uses the parameter list to identify the function to alter.

OWNER TO new-owner

Transfers function ownership to another user.

RENAME TO new-name

Renames this function.

SET SCHEMA new-schema

Moves the function to another schema.

Privileges

Non-superuser: USAGE on the schema and one of the following:

• Function owner

• ALTER privilege on the function

For certain operations, non-superusers must also have the following schema privileges:

See also

7.2 - ALTER ANALYTIC FUNCTION

Alters a user-defined analytic function.

Syntax

ALTER ANALYTIC FUNCTION [[db-name.]schema.]function-name( [ parameter-list ] ) {
    OWNER TO new-owner
    | RENAME TO new-name
    | SET FENCED boolean-expr
    | SET SCHEMA new-schema
}

Parameters

[db-name.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function-name

Name of the function to alter.

parameter-list

Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.

Note

Vertica supports function overloading, and uses the parameter list to identify the function to alter.

OWNER TO new-owner

Transfers function ownership to another user.

RENAME TO new-name

Renames this function.

SET FENCED { true | false }

Specifies whether to enable fenced mode for this function.

SET SCHEMA new-schema

Moves the function to another schema.

Privileges

Non-superuser: USAGE on the schema and one of the following:

• Function owner

• ALTER privilege on the function

For certain operations, non-superusers must also have the following schema privileges:

See also

7.3 - ALTER FILTER

Alters a user-defined filter.

Syntax

ALTER FILTER [[db-name.]schema.]function-name( [ parameter-list ] ) {
    OWNER TO new-owner
    | RENAME TO new-name
    | SET FENCED boolean-expr
    | SET SCHEMA new-schema
}

Parameters

[db-name.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function-name

Name of the function to alter.

parameter-list

Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.

Note

Vertica supports function overloading, and uses the parameter list to identify the function to alter.

OWNER TO new-owner

Transfers function ownership to another user.

RENAME TO new-name

Renames this function.

SET FENCED { true | false }

Specifies whether to enable fenced mode for this function.

SET SCHEMA new-schema

Moves the function to another schema.

Privileges

Non-superuser: USAGE on the schema and one of the following:

• Function owner

• ALTER privilege on the function

For certain operations, non-superusers must also have the following schema privileges:

See also

7.4 - ALTER FUNCTION (scalar)

Alters a user-defined scalar function.

Syntax

ALTER FUNCTION [[db-name.]schema.]function-name( [ parameter-list] ) {
    OWNER TO new-owner
    | RENAME TO new-name
    | SET FENCED boolean-expr
    | SET SCHEMA new-schema
}

Parameters

[db-name.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function-name

Name of the function to alter.

parameter-list

Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.

Note

Vertica supports function overloading, and uses the parameter list to identify the function to alter.

OWNER TO new-owner

Transfers function ownership to another user.

RENAME TO new-name

Renames this function.

SET FENCED { true | false }

Specifies whether to enable fenced mode for this function.

SET SCHEMA new-schema

Moves the function to another schema.

Privileges

Non-superuser: USAGE on the schema and one of the following:

• Function owner

• ALTER privilege on the function

For certain operations, non-superusers must also have the following schema privileges:

Examples

Rename function UDF_one to UDF_two:

=> ALTER FUNCTION UDF_one (int, int) RENAME TO UDF_two;

Move function UDF_two to schema macros:

=> ALTER FUNCTION UDF_two (int, int) SET SCHEMA macros;

Disable fenced mode for function UDF_two:

=> ALTER FUNCTION UDF_two (int, int) SET FENCED false;

See also

7.5 - ALTER FUNCTION (SQL)

Alters a user-defined SQL function.

Syntax

ALTER FUNCTION [[db-name.]schema.]function-name( [arg-list] ) {
    OWNER TO new-owner
    | RENAME TO new-name
    | SET SCHEMA new-schema
}

Parameters

[db-name.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function-name

The name of the SQL function to alter.

arg-list

A comma-delimited list of function argument names. If none, specify an empty list.

OWNER TO new-owner

Transfers function ownership to another user.

RENAME TO new-name

Renames this function.

SET SCHEMA new-schema

Moves the function to another schema.

Privileges

Non-superuser: USAGE on the schema and one of the following:

• Function owner

• ALTER privilege on the function

For certain operations, non-superusers must also have the following schema privileges:

Examples

Rename function SQL_one to SQL_two:

=> ALTER FUNCTION SQL_one (int, int) RENAME TO SQL_two;

Move function SQL_two to schema macros:

=> ALTER FUNCTION SQL_two (int, int) SET SCHEMA macros;

Reassign ownership of SQL_two:

=> ALTER FUNCTION SQL_two (int, int) OWNER TO user1;

See also

• CREATE FUNCTION (SQL)
• User-defined SQL functions

7.6 - ALTER PARSER

Alters a user-defined parser.

Syntax

ALTER PARSER [[db-name.]schema.]function-name( [ parameter-list ] ) {
    OWNER TO new-owner
    | RENAME TO new-name
    | SET FENCED boolean-expr
    | SET SCHEMA new-schema
}

Parameters

[db-name.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function-name

Name of the function to alter.

parameter-list

Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.

Note

Vertica supports function overloading, and uses the parameter list to identify the function to alter.

OWNER TO new-owner

Transfers function ownership to another user.

RENAME TO new-name

Renames this function.

SET FENCED { true | false }

Specifies whether to enable fenced mode for this function.

SET SCHEMA new-schema

Moves the function to another schema.

Privileges

Non-superuser: USAGE on the schema and one of the following:

• Function owner

• ALTER privilege on the function

For certain operations, non-superusers must also have the following schema privileges:

See also

7.7 - ALTER SOURCE

Alters a user-defined load source function.

Syntax

ALTER SOURCE [[db-name.]schema.]function-name( [ parameter-list ] ) {
    OWNER TO new-owner
    | RENAME TO new-name
    | SET FENCED boolean-expr
    | SET SCHEMA new-schema
}

Parameters

[db-name.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function-name

Name of the function to alter.

parameter-list

Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.

Note

Vertica supports function overloading, and uses the parameter list to identify the function to alter.

OWNER TO new-owner

Transfers function ownership to another user.

RENAME TO new-name

Renames this function.

SET FENCED { true | false }

Specifies whether to enable fenced mode for this function.

SET SCHEMA new-schema

Moves the function to another schema.

Privileges

Non-superuser: USAGE on the schema and one of the following:

• Function owner

• ALTER privilege on the function

For certain operations, non-superusers must also have the following schema privileges:

See also

7.8 - ALTER TRANSFORM FUNCTION

Alters a user-defined transform function.

Syntax

ALTER TRANSFORM FUNCTION [[db-name.]schema.]function-name( [ parameter-list ] ) {
    OWNER TO new-owner
    | RENAME TO new-name
    | SET FENCED { true | false }
    | SET SCHEMA new-schema
}

Parameters

[db-name.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function-name

Name of the function to alter.

parameter-list

Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.

Note

Vertica supports function overloading, and uses the parameter list to identify the function to alter.

OWNER TO new-owner

Transfers function ownership to another user.

RENAME TO new-name

Renames this function.

SET FENCED { true | false }

Specifies whether to enable fenced mode for this function.

SET SCHEMA new-schema

Moves the function to another schema.

Privileges

Non-superuser: USAGE on the schema and one of the following:

• Function owner

• ALTER privilege on the function

For certain operations, non-superusers must also have the following schema privileges:

See also

8 - ALTER HCATALOG SCHEMA

Alters parameter values on a schema that was created with CREATE HCATALOG SCHEMA. HCatalog schemas are used by the HCatalog Connector to access data stored in a Hive data warehouse. For more information, see Using the HCatalog Connector.

Some parameters cannot be altered after creation. If you need to change one of those values, delete and recreate the schema instead. You can use ALTER HCATALOG SCHEMA to change the following parameters:

• HOSTNAME

• PORT

• HIVESERVER2_HOSTNAME

• WEBSERVICE_HOSTNAME

• WEBSERVICE_PORT

• WEBHDFS_ADDRESS

• HCATALOG_CONNECTION_TIMEOUT

• HCATALOG_SLOW_TRANSFER_LIMIT

• HCATALOG_SLOW_TRANSFER_TIME

• SSL_CONFIG

• CUSTOM_PARTITIONS

Syntax

ALTER HCATALOG SCHEMA schema-name SET [param=value]+;

Parameters

Privileges

One of the following:

• Superuser

• Schema owner

Examples

The following example shows how to change the Hive metastore hostname and port for the "hcat" schema. In this example, Hive uses High Availability metastore.

=> ALTER HCATALOG SCHEMA hcat SET HOSTNAME='thrift://ms1.example.com:9083,thrift://ms2.example.com:9083';

The following example shows the error you receive if you try to set an unalterable parameter.

=> ALTER HCATALOG SCHEMA hcat SET HCATALOG_USER='admin';
   ERROR 4856: Syntax error at or near "HCATALOG_USER" at character 39

9 - ALTER LIBRARY

Replaces the library file that is currently associated with a UDx library in the Vertica catalog. Vertica automatically distributes copies of the updated file to all cluster nodes. UDxs defined in the catalog that reference the updated library automatically start using the updated library file. A UDx is considered to be the same if its name and signature match.

The current and replacement libraries must be written in the same language.

Syntax

ALTER LIBRARY [[database.]schema.]name [DEPENDS 'depends-path'] AS 'path';

Arguments

schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

name

The name of an existing library created with CREATE LIBRARY.

DEPENDS 'depends-path'

Files or libraries on which this library depends, one or more files or directories on the initiator node file system or other supported file systems or object stores. For a directory, end the path entry with a slash (/), optionally followed by a wildcard (*). To specify more than one file, separate entries with colons (:).

If any path entry contain colons, such as a URI, place brackets around the entire DEPENDS path and use double quotes for the individual path elements, as in the following example:

DEPENDS '["s3://mybucket/gson-2.3.1.jar"]'

To specify libraries with multiple directory levels, see Multi-level Library Dependencies.

DEPENDS has no effect for libraries written in R. R packages must be installed locally on each node, including external dependencies.

Important

The performance of CREATE LIBRARY can degrade in Eon Mode, in proportion to the number and depth of dependencies specified by the DEPENDS clause.

AS path

The absolute path on the initiator node file system of the replacement library file.

Privileges

Superuser, or UDXDEVELOPER and CREATE on the schema. Non-superusers must explicitly enable the UDXDEVELOPER role. See CREATE LIBRARY for examples.

Multi-level library dependencies

If a DEPENDS clause specifies a library with multiple directory levels, Vertica follows the library path to include all subdirectories of that library. For example, the following CREATE LIBRARY statement enables the UDx library mylib to import all Python packages and modules that it finds in subdirectories of site-packages:

=> CREATE LIBRARY mylib AS '/path/to/python_udx' DEPENDS '/path/to/python/site-packages' LANGUAGE 'Python';

Examples

This example shows how to update an already-defined library named myFunctions with a new file.

=> ALTER LIBRARY myFunctions AS '/home/dbadmin/my_new_functions.so';

See also

10 - ALTER LOAD BALANCE GROUP

Changes the configuration of a load balance group.

Syntax

ALTER LOAD BALANCE GROUP group-name {
    RENAME TO new-name |
    SET FILTER TO 'ip-cidr-addr' |
    SET POLICY TO 'policy' |
    ADD {ADDRESS | FAULT GROUP | SUBCLUSTER} add-list |
    DROP  {ADDRESS | FAULT GROUP | SUBCLUSTER} drop-list
}

Parameters

group-name

Name of an existing load balance group to change.

RENAME TO new-name

Renames the group to new-name.

SET FILTER TO 'ip-cidr-addr'

An IPv4 or IPv6 CIDR to replace the existing IP address filter that selects which members of a fault group or subcluster to include in the load balance group. This setting is only valid if the load balance group contains fault groups or subclusters.

SET POLICY TO 'policy'

Changes the policy the load balance group uses to select the target node for the incoming connection. One of:

• ROUNDROBIN

• RANDOM

• NONE

See CREATE LOAD BALANCE GROUP for details.

ADD {ADDRESS | FAULT GROUP | SUBCLUSTER }

Adds objects of the specified type to the load balance group. Load balance groups can only contain one type of object. For example, if you created the load balance group using a list of addresses, you can only add additional addresses, not fault groups or subclusters.

add-list

A comma-delimited list of objects (addresses, fault groups, or subclusters) to add to the fault group.

DROP {ADDRESS | FAULT GROUP | SUBCLUSTER}

Removes objects of the specified type from the load balance group (addresses, fault groups, or subclusters). The object type must match the type of the objects already in the load balance group.

drop-list

The list of objects to remove from the load balance group.

Privileges

Superuser

Examples

Remove an address from the load balance group named group_2.

=> SELECT * FROM LOAD_BALANCE_GROUPS;
  name   |   policy   | filter |         type          | object_name
---------+------------+--------+-----------------------+-------------
 group_1 | ROUNDROBIN |        | Network Address Group | node01
 group_1 | ROUNDROBIN |        | Network Address Group | node02
 group_2 | ROUNDROBIN |        | Network Address Group | node03
(3 rows)

=> ALTER LOAD BALANCE GROUP group_2 DROP ADDRESS node03;
ALTER LOAD BALANCE GROUP

=> SELECT * FROM LOAD_BALANCE_GROUPS;
  name   |   policy   | filter |         type          | object_name
---------+------------+--------+-----------------------+-------------
 group_1 | ROUNDROBIN |        | Network Address Group | node01
 group_1 | ROUNDROBIN |        | Network Address Group | node02
 group_2 | ROUNDROBIN |        | Empty Group           |
(3 rows)

The following example adds three network addresses to the group named group_2:

=> ALTER LOAD BALANCE GROUP group_2 ADD ADDRESS node01,node02,node03;
ALTER LOAD BALANCE GROUP
=> SELECT * FROM load_balance_groups WHERE name = 'group_2';
-[ RECORD 1 ]----------------------
name        | group_2
policy      | ROUNDROBIN
filter      |
type        | Network Address Group
object_name | node01
-[ RECORD 2 ]----------------------
name        | group_2
policy      | ROUNDROBIN
filter      |
type        | Network Address Group
object_name | node02
-[ RECORD 3 ]----------------------
name        | group_2
policy      | ROUNDROBIN
filter      |
type        | Network Address Group
object_name | node03

See also

• ALTER NETWORK ADDRESS
• ALTER ROUTING RULE
• CREATE LOAD BALANCE GROUP
• LOAD_BALANCE_GROUPS
• NETWORK_ADDRESSES

11 - ALTER MODEL

Allows users to rename an existing model, change ownership, or move it to a another schema.

Syntax

ALTER MODEL [[database.]schema.]model
    { OWNER TO owner
    | RENAME TO new-name
    | SET SCHEMA schema
    | { INCLUDE | EXCLUDE | MATERIALIZE } [ SCHEMA ] PRIVILEGES }

Parameters

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

model

Identifies the model to alter.

OWNER TO owner

Reassigns ownership of this model to owner. If a non-superuser, you must be the current owner.

RENAME TO

Renames the mode, where new-name conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.

SET SCHEMA schema

Moves the model from one schema to another. If privilege inheritance is enabled with INCLUDE SCHEMA PRIVILEGES, the model inherits the privileges of its new parent schema.

[ { INCLUDE | EXCLUDE | MATERIALIZE } [ SCHEMA ] PRIVILEGES ]

The INCLUDE and EXCLUDE parameters determine whether the model inherits the privileges of its parent schema, overriding the schema-level setting:

• INCLUDE SCHEMA PRIVILEGES: The model inherits the privileges of its parent schema. This parameter has no effect while privilege inheritance is disabled at the database level with DisableInheritedPrivileges.

• EXCLUDE SCHEMA PRIVILEGES: The model does not inherit the privileges of its parent schema.

The MATERIALIZE parameter converts inherited privileges into explicit grants on the model. If privilege inheritance is disabled at the database level with DisableInheritedPrivileges, MATERIALIZE converts the set of inherited privileges that would be on the model if privilege inheritance was enabled.

Privileges

Non-superuser: USAGE on the schema and one of the following:

• Model owner

• ALTER privilege on the model

For certain operations, non-superusers must have the following schema privileges:

Examples

To move a model to another schema:

=> ALTER MODEL my_kmeans_model SET SCHEMA clustering_models;
ALTER MODEL

To rename a model:

=> ALTER MODEL my_kmeans_model RENAME TO kmeans_model;

To change the owner of the model:

=> ALTER MODEL kmeans_model OWNER TO analytics_user;

12 - ALTER NETWORK ADDRESS

Changes the configuration of an existing network address.

Syntax

ALTER NETWORK ADDRESS name {
    RENAME TO new-name
    | SET TO 'ip-addr' [PORT port-number]
    | { ENABLE | DISABLE }
    }

Parameters

name

Name of an existing network address to change.

RENAME TO new-name

Renames the network address to new-name. This name change has no effect on the network address's membership in load balance groups.

SET TO 'ip-addr'

Changes the IP address assigned to the network address.

PORT port-number

Sets the port number for the network address. You must supply a network address when altering the port number.

ENABLE | DISABLE

Enables or disables the network address.

Examples

Rename the network address from test_addr to alt_node1, then change its IP address to 192.168.1.200 with port number 4000:

=> ALTER NETWORK ADDRESS test_addr RENAME TO alt_node1;
ALTER NETWORK ADDRESS
=> ALTER NETWORK ADDRESS alt_node1 SET TO '192.168.1.200' PORT 4000;
ALTER NETWORK ADDRESS

See also

• ALTER LOAD BALANCE GROUP
• ALTER ROUTING RULE
• CREATE LOAD BALANCE GROUP
• LOAD_BALANCE_GROUPS
• NETWORK_ADDRESSES

13 - ALTER NETWORK INTERFACE

Renames a network interface.

Syntax

ALTER NETWORK INTERFACE network-interface-name RENAME TO new-network-interface-name

Parameters

network-interface-name

The name of the existing network interface.

new-network-interface-name

The new name for the network interface.

Privileges

Superuser

Examples

Rename a network interface:

=> ALTER NETWORK INTERFACE myNetwork RENAME TO myNewNetwork;

14 - ALTER NODE

Sets and clears node-level configuration parameters on the specified node. ALTER NODE also performs the following management tasks:

• Changes the node type.

• Specifies the network interface of the public network on individual nodes that are used for import and export.

• Replaces a down node.

For information about removing a node, see

• Removing nodes from a database
• Removing hosts from a cluster

Syntax

ALTER NODE node-name {
    EXPORT ON { network-interface | DEFAULT }
    | [IS] node-type
    | REPLACE [ WITH standby-node ]
    | RESET
    | SET [PARAMETER] parameter=value[,...]
    | CLEAR [PARAMETER] parameter[,...]
}

Parameters

node-name

The name of the node to alter.

[IS] node-type

Changes the node type, where node-type is one of the following:

• PERMANENT: (default): A node that stores data.

• EPHEMERAL: A node that is in transition from one type to another—typically, from PERMANENT to either STANDBY or EXECUTE.

• STANDBY: A node that is reserved to replace any node when it goes down. A standby node stores no segments or data until it is called to replace a down node. When used as a replacement node, Vertica changes its type to PERMANENT. For more information, see Active standby nodes.

• EXECUTE: A node that is reserved for computation purposes only. An execute node contains no segments or data.

Note

STANDBY and EXECUTE node types are supported only in Enterprise Mode.

EXPORT ON

Specifies the network to use for importing and exporting data, one of the following:

• network-interface: The name of a network interface of the public network.

• DEFAULT: Use the default network interface of the public network, as specified by ALTER DATABASE.

REPLACE [WITH standby-node]

Enterprise Mode only, replaces the specified node with an available active standby node. If you omit the WITH clause, Vertica tries to find a replacement node from the same fault group as the down node.

If you specify a node that is not down, Vertica ignores this statement.

RESET

Enterprise Mode only, restores the specified down node and returns its replacement to standby status. If the down node cannot resume activity, Vertica ignores this statement and leaves the standby node in place.

SET [PARAMETER]

Sets one or more configuration parameters to the specified value at the node level.

CLEAR [PARAMETER]

Clears one or more specified configuration parameters.

Privileges

Superuser

Examples

Specify to use the default network interface of public network on v_vmart_node0001 for import/export operations:

=> ALTER NODE v_vmart_node0001 EXPORT ON DEFAULT;

Replace down node v_vmart_node0001 with an active standby node, then restore it:

=> ALTER NODE v_vmart_node0001 REPLACE WITH standby1;
...
=> ALTER NODE v_vmart_node0001 RESET;

Set and clear configuration parameter MaxClientSessions:

=> ALTER NODE v_vmart_node0001 SET MaxClientSessions = 0;
...
=> ALTER NODE v_vmart_node0001 CLEAR MaxClientSessions;

Set the node type as EPHEMERAL:

=> ALTER NODE v_vmart_node0001 IS EPHEMERAL;

15 - ALTER NOTIFIER

Updates an existing notifier.

Syntax

ALTER NOTIFIER notifier-name
    [ ENABLE | DISABLE ]
    [ MAXPAYLOAD 'max-payload-size' ]
    [ MAXMEMORYSIZE 'max-memory-size' ]
    [ TLS CONFIGURATION tls-configuration ]
    [ TLSMODE 'tls-mode' ]
    [ CA BUNDLE bundle-name [ CERTIFICATE certificate-name ] ]
    [ IDENTIFIED BY 'uuid' ]
    [ [NO] CHECK COMMITTED ]
    [ PARAMETERS 'adapter-params' ]

Parameters

notifier-name

Specifies the notifier to update.

[NO] CHECK COMMITTED

Specifies to wait for delivery confirmation before sending the next message in the queue. Not all messaging systems support delivery confirmation.

ENABLE | DISABLE

Specifies whether to enable or disable the notifier.

MAXPAYLOAD

The maximum size of the message, up to 2 TB, specified in kilobytes, megabytes, gigabytes, or terabytes as follows:

MAXPAYLOAD integer{K|M|G|T}

The default setting is adapter-specific—for example, 1 M for Kafka.

Changes to this parameter take effect either after the notifier is disabled and reenabled or after the database restarts.

MAXMEMORYSIZE

The maximum size of the internal notifier, up to 2 TB, specified in kilobytes, megabytes, gigabytes, or terabytes as follows:

MAXMEMORYSIZE integer{K|M|G|T}

If the queue exceeds this size, the notifier drops excess messages.

TLS CONFIGURATION tls-configuration

The TLS CONFIGURATION to use for TLS.

Notifiers support the following TLS modes:

• DISABLE

• TRY_VERIFY (behaves like VERIFY_CA)

• VERIFY_CA

• VERIFY_FULL

If the notifier ACTION is 'syslog' or 'sns', this parameter has no effect.

To encrypt messages sent to syslog, you must configure syslog for TLS.

To encrypt messages sent to an SNS endpoint, you must set the following configuration parameters:

• SNSCAFile or AWSCAFile

• SNSCAPath or AWSCAPath

• SNSEnableHttps

TLSMODE 'tls-mode'

Deprecated

This parameter has been superseded by the TLS CONFIGURATION parameter. If you use this parameter while the TLS CONFIGURATION parameter is not set, Vertica automatically creates a new TLS Configuration for the notifier uses the same values as the deprecated parameter.

Specifies the type of connection between the notifier and an endpoint, one of the following:

• disable (default): Plaintext connection.

• verify-ca: Encrypted connection, and the server's certificate is verified as being signed by a trusted CA.

If you set this parameter to verify-ca, the generated TLS Configuration will be set to TRY_VERIFY, which has the same behavior as VERIFY_CA.

If the notifier ACTION is 'syslog' or 'sns', this parameter has no effect.

To encrypt messages sent to syslog, you must configure syslog for TLS.

To encrypt messages sent to an SNS endpoint, you must set the following configuration parameters:

• SNSCAFile or AWSCAFile

• SNSCAPath or AWSCAPath

• SNSEnableHttps

CA BUNDLE bundle-name

Deprecated

This parameter has been superseded by the TLS CONFIGURATION parameter. If you use this parameter while the TLS CONFIGURATION parameter is not set, Vertica automatically creates a new TLS Configuration for the notifier uses the same values as the deprecated parameter.

Specifies a CA bundle. The certificates inside the bundle are used to validate the Kafka server's certificate if the TLSMODE requires it.

If a CA bundle is specified for a notifier that currently uses disable, which doesn't validate the Kafka server's certificate, the bundle will go unused when connecting to the Kafka server. This behavior persists unless the TLSMODE is changed to one that validates server certificates.

Changes to contents of the CA bundle take effect either after the notifier is disabled and re-enabled or after the database restarts. However, changes to which CA bundle the notifier uses takes effect immediately.

If the notifier ACTION is 'syslog' or 'sns', this parameter has no effect.

To encrypt messages sent to syslog, you must configure syslog for TLS.

To encrypt messages sent to an SNS endpoint, you must set the following configuration parameters:

• SNSCAFile or AWSCAFile

• SNSCAPath or AWSCAPath

• SNSEnableHttps

CERTIFICATE certificate-name

Deprecated

This parameter has been superseded by the TLS CONFIGURATION parameter. If you use this parameter while the TLS CONFIGURATION parameter is not set, Vertica automatically creates a new TLS Configuration for the notifier uses the same values as the deprecated parameter.

Specifies a client certificate for validation by the endpoint.

If the notifier ACTION is 'syslog' or 'sns', this parameter has no effect.

To encrypt messages sent to syslog, you must configure syslog for TLS.

To encrypt messages sent to an SNS endpoint, you must set the following configuration parameters:

• SNSCAFile or AWSCAFile

• SNSCAPath or AWSCAPath

• SNSEnableHttps

IDENTIFIED BY 'uuid'

Specifies the notifier's unique identifier. If set, all the messages published by this notifier have this attribute.

PARAMETERS 'adapter-params'

Specifies one or more optional adapter parameters that are passed as a string to the adapter. Adapter parameters apply only to the adapter associated with the notifier.

Changes to this parameter take effect either after the notifier is disabled and reenabled or after the database restarts.

For Kafka notifiers, refer to Kafka and Vertica configuration settings.

Privileges

Superuser

Encrypted notifiers for SASL_SSL Kafka configurations

Follow this procedure to create or alter notifiers for Kafka endpoints that use SASL_SSL. Note that you must repeat this procedure whenever you change the TLSMODE, certificates, or CA bundle for a given notifier.

1. Create a TLS Configuration with the desired TLS mode, certificate, and CA certificates.

2. Use CREATE or ALTER to disable the notifier and set the TLS Configuration:

   => ALTER NOTIFIER encrypted_notifier
       DISABLE
       TLS CONFIGURATION kafka_tls_config;
   

3. ALTER the notifier and set the proper rdkafka adapter parameters for SASL_SSL:

   => ALTER NOTIFIER encrypted_notifier PARAMETERS
       'sasl.username=user;sasl.password=password;sasl.mechanism=PLAIN;security.protocol=SASL_SSL';
   

4. Enable the notifier:

   => ALTER NOTIFIER encrypted_notifier ENABLE;
   

Examples

Update the settings on an existing notifier:

=> ALTER NOTIFIER my_dc_notifier
    ENABLE
    MAXMEMORYSIZE '2G'
    IDENTIFIED BY 'f8b0278a-3282-4e1a-9c86-e0f3f042a971'
    CHECK COMMITTED;

Add a TLS Configuration to a notifier. To create a custom TLS Configuration, see TLS configurations:

=> ALTER NOTIFIER my_notifier TLS CONFIGURATION notifier_tls_config

See also

• CREATE NOTIFIER
• DROP NOTIFIER
• Monitoring Vertica using notifiers

16 - ALTER PROCEDURE (stored)

Alters a stored procedure, retaining any existing grants.

Syntax

ALTER PROCEDURE procedure ( [ [ parameter_mode ] [ parameter ] parameter_type [, ...] ] )
    [ SECURITY { INVOKER | DEFINER }
      | RENAME TO new_procedure_name
      | OWNER TO new_owner
      | SET SCHEMA new_schema
      | SOURCE TO new_source
    ]

Parameters

procedure

The procedure to alter.

parameter_mode

The IN and INOUT parameters of the stored procedure.

parameter

The name of the parameter.

parameter_type

The type of the parameter.

SECURITY { INVOKER | DEFINER }

Specifies whether to execute the procedure with the privileges of the invoker or its definer (owner).

For details, see Executing stored procedures.

RENAME TO new_procedure_name

The new name for the procedure.

OWNER TO new_owner

The new owner (definer) of the procedure.

SET SCHEMA new_schema

The new schema of the procedure.

SOURCE TO new_source

The new procedure source code. For details, see Scope and structure.

Privileges

OWNER TO

Superuser

RENAME and SCHEMA TO

Non-superuser:

• CREATE on the procedure's schema

• Ownership of the procedure

Other operations

Non-superuser: Ownership of the procedure

Examples

See Altering stored procedures.

17 - ALTER PROFILE

Changes a profile. All parameters that are not set in a profile inherit their setting from the default profile. You can use ALTER PROFILE to change the default profile.

Syntax

ALTER PROFILE name LIMIT [
    PASSWORD_LIFE_TIME setting
    PASSWORD_MIN_LIFE_TIME setting
    PASSWORD_GRACE_TIME setting
    FAILED_LOGIN_ATTEMPTS setting
    PASSWORD_LOCK_TIME setting
    PASSWORD_REUSE_MAX setting
    PASSWORD_REUSE_TIME setting
    PASSWORD_MAX_LENGTH setting
    PASSWORD_MIN_LENGTH setting
    PASSWORD_MIN_LETTERS setting
    PASSWORD_MIN_UPPERCASE_LETTERS setting
    PASSWORD_MIN_LOWERCASE_LETTERS setting
    PASSWORD_MIN_DIGITS setting
    PASSWORD_MIN_SYMBOLS setting
    PASSWORD_MIN_CHAR_CHANGE setting ]

Parameters

Privileges

Superuser

Profile settings and client authentication

The following profile settings affect client authentication methods, such as LDAP or GSS:

• FAILED_LOGIN_ATTEMPTS

• PASSWORD_LOCK_TIME

All other profile settings are used only by Vertica to manage its passwords.

Examples

ALTER PROFILE sample_profile LIMIT FAILED_LOGIN_ATTEMPTS 3;

See also

• CREATE PROFILE
• DROP PROFILE
• Creating a database name and password

18 - ALTER PROFILE RENAME

Rename an existing profile.

Syntax

ALTER PROFILE name RENAME TO new-name;

Parameters

name

The current name of the profile.

new-name

The new name for the profile.

Privileges

Superuser

Examples

This example shows how to rename an existing profile.

ALTER PROFILE sample_profile RENAME TO new_sample_profile;

See also

• ALTER PROFILE
• CREATE PROFILE
• DROP PROFILE

19 - ALTER PROJECTION

Changes the DDL of the specified projection.

Syntax

ALTER PROJECTION [[database.]schema.]projection
   { RENAME TO new-name | ON PARTITION RANGE BETWEEN min-val AND max-val | { ENABLE | DISABLE } }
 

Parameters

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

projection

The projection to change, where projection can be one of the following:

• Projection base name: Rename all projections that share this base name.

• Projection name: Rename the specified projection and its base name. If the projection is segmented, its buddies are unaffected by this change.

See Projection naming for projection name conventions.

RENAME TOnew-name

The new projection name.

ON PARTITION RANGE

Note

Valid only for projections that were created with a partition range.

Specifies to limit data of this projection to a range of partition keys, specified as follows:

ON PARTITION RANGE BETWEEN min-range-value AND max-range-value

where the following requirements apply to min-range-value and ≤ max-range-value:

• min-range-value must be ≤ max-range-value

• They must resolve to a data type that is compatible with the table partition expression.

• They can be:

  • String literals—for example, 2021-07-31

  • Expressions with stable or immutable functions, for example:

    date_trunc('month', now()::timestamp - interval'1 month')
    

max-range-value can be set to NULL, to specify that the partition range has no upper bound.

min-range-value can be set to NULL, to specify that the partition range has no lower bound.

If both partition range projection min-range-value and max-range-value are set to NULL, it will drop the projection endpoints, becoming a regular projection.

If the new range of keys is outside the previous range, Vertica throws a warning that the projection is out of date and must be refreshed before it can be used.

For other requirements and usage details, see Partition range projections.

ENABLE | DISABLE

Specifies whether to mark this projection as unavailable for queries on its anchor table. If a projection is the queried table's only superprojection, attempts to disable it return with a rollback message. ENABLE restores the projection's availability to query planning. You can also mark a projection as unavailable for individual queries using the hint SKIP_PROJS.

Default: ENABLE

Privileges

Non-superuser, CREATE and USAGE on the schema and one of the following anchor table privileges:

• Table owner

• ALTER privilege

• SELECT privilege only if defining a partition range projection

Syntactic sugar

The statement

=> ALTER PROJECTION foo REMOVE PARTITION RANGE;

has the same effect as

=> ALTER PROJECTION foo ON PARTITION RANGE BETWEEN NULL AND NULL;

Examples

=> SELECT export_tables('','public.store_orders');

                export_tables
---------------------------------------------

CREATE TABLE public.store_orders
(
    order_no int,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date NOT NULL
);
(1 row)

=> CREATE PROJECTION store_orders_p AS SELECT * from store_orders;
CREATE PROJECTION
=> ALTER PROJECTION store_orders_p RENAME to store_orders_new;
ALTER PROJECTION
=> ALTER PROJECTION store_orders_new DISABLE;
=> SELECT * FROM store_orders_new;
ERROR 3586:  Insufficient projections to answer query
DETAIL:  No projections eligible to answer query
HINT:  Projection store_orders_new not used in the plan because the projection is disabled.
=> ALTER PROJECTION store_orders_new ENABLE;

See also

20 - ALTER RESOURCE POOL

Modifies an existing resource pool by setting one or more parameters.

You can use ALTER RESOURCE POOL to modify some parameters in Vertica built-in resource pools. For details on default settings and restrictions, see Built-in resource pools configuration.

Syntax

ALTER RESOURCE POOL pool-name [ FOR subcluster ] parameter-name setting[...]

Arguments

pool-name

Name of the resource pool to modify.

FOR subcluster

Eon Mode only, the subcluster to associate with this resource pool, where subcluster is one of the following:

• SUBCLUSTER subcluster-name: Resource pool for an existing subcluster. You cannot be connected to this subcluster, otherwise Vertica returns an error.
• CURRENT SUBCLUSTER: Resource pool for the subcluster that you are connected to.

Important

You cannot use ALTER RESOURCE POOL to convert a global resource pool to a subcluster-level resource pool.

parameter-name setting

A resource pool parameter and its new setting. To reset this parameter to its default value, specify DEFAULT.

If you specify a subcluster, you can alter only the MAXMEMORYSIZE, MAXQUERYMEMORYSIZE, and MEMORYSIZE parameters for built-in pools.

Parameters

CASCADE TO

Secondary resource pool for executing queries that exceed the RUNTIMECAP setting of their assigned resource pool:

CASCADE TO secondary-pool

CPUAFFINITYMODE

Specifies whether the resource pool has exclusive or shared use of the CPUs specified in CPUAFFINITYSET:

CPUAFFINITYMODE { SHARED | EXCLUSIVE | ANY }

• SHARED: Queries that run in this resource pool share its CPUAFFINITYSET CPUs with other Vertica resource pools.
• EXCLUSIVE: Dedicates CPUAFFINITYSET CPUs to this resource pool only, and excludes other Vertica resource pools. If CPUAFFINITYSET is set as a percentage, then that percentage of CPU resources available to Vertica is assigned solely for this resource pool.
• ANY: Queries in this resource pool can run on any CPU, invalid if CPUAFFINITYSET designates CPU resources.

Default: ANY

CPUAFFINITYSET

CPUs available to this resource pool. All cluster nodes must have the same number of CPUs. The CPU resources assigned to this set are unavailable to general resource pools.

CPUAFFINITYSET {
  'cpu-index[,...]'
| 'cpu-indexi-cpu-indexn'
| 'integer%'
| NONE
}

• cpu-index[,...]: Dedicates one or more comma-delimited CPUs to this resource pool.
• cpu-indexi-cpu-indexn: Dedicates a range of contiguous CPU indexes i through n to this resource pool.
• integer%: Percentage of all available CPUs to use for this resource pool. Vertica rounds this percentage down to include whole CPU units.
• NONE (empty string): No affinity set is assigned to this resource pool. Queries associated with this pool are executed on any CPU.

Default: NONE

Important

CPUAFFINITYSET and CPUAFFINITYMODE must be set together in the same statement.

EXECUTIONPARALLELISM

Number of threads used to process any single query issued in this resource pool.

EXECUTIONPARALLELISM { limit | AUTO }

• limit: An integer value between 1 and the number of cores. Setting this parameter to a reduced value increases throughput of short queries issued in the resource pool, especially if queries are executed concurrently.
• AUTO or 0: Vertica calculates the setting from the number of cores, available memory, and amount of data in the system. Unless memory is limited, or the amount of data is very small, Vertica sets this parameter to the number of cores on the node.

Default: AUTO

MAXCONCURRENCY

Maximum number of concurrent execution slots available to the resource pool across the cluster:

MAXCONCURRENCY { integer | NONE }

NONE (empty string): Unlimited number of concurrent execution slots.

Default: NONE

MAXMEMORYSIZE

Maximum size per node the resource pool can grow by borrowing memory from the GENERAL pool:

MAXMEMORYSIZE {
  'integer%'
  |'integer{K|M|G|T}'
  NONE
}

• integer%: Percentage of total memory
• integer{K|M|G|T}: Amount of memory in kilobytes, megabytes, gigabytes, or terabytes
• NONE (empty string): Unlimited, resource pool can borrow any amount of available memory from the GENERAL pool.

Default: NONE

MAXQUERYMEMORYSIZE

Maximum amount of memory this resource pool can allocate at runtime to process a query. If the query requires more memory than this setting, Vertica stops execution and returns an error.

Set this parameter as follows:

MAXQUERYMEMORYSIZE {
  'integer%'
| 'integer{K|M|G|T}'
| NONE
}

• integer%: Percentage of MAXMEMORYSIZE for this resource pool.
• integer{K|M|G|T}: Amount of memory in kilobytes, megabytes, gigabytes, or terabytes, up to the value of MAXMEMORYSIZE.
• NONE (empty string): Unlimited; resource pool can borrow any amount of available memory from the GENERAL pool, within the limits set by MAXMEMORYSIZE.

Default: NONE

Important

Changes to MAXQUERYMEMORYSIZE are applied retroactively to queries that are currently executing. If you reduce this setting, queries that were budgeted with the previous memory size are liable to fail if they try to allocate more memory than the new setting allows.

MEMORYSIZE

Total per-node memory available to the Vertica resource manager that is allocated to this resource pool:

MEMORYSIZE {
  'integer%'
| 'integer{K|M|G|T}'
}

• integer%: Percentage of total memory
• integer{K|M|G|T}: Amount of memory in kilobytes, megabytes, gigabytes, or terabytes

Default: 0%. No memory allocated, the resource pool borrows memory from the GENERAL pool.

PLANNEDCONCURRENCY

Preferred number of queries to execute concurrently in the resource pool. This setting applies to the entire cluster:

PLANNEDCONCURRENCY { num-queries | AUTO }

• num-queries: Integer value ≥ 1, the preferred number of queries to execute concurrently in the resource pool. When possible, query resource budgets are limited to allow this level of concurrent execution.

• AUTO: Value is calculated automatically at query runtime. Vertica sets this parameter to the lower of these two calculations, but never less than 4:

  • Number of logical cores

  • Memory divided by 2GB

  If the number of logical cores on each node is different, AUTO is calculated differently for each node. Distributed queries run like the minimal effective planned concurrency. Single node queries run with the planned concurrency of the initiator.

Default: AUTO

Tip

Change this parameter only after evaluating performance over a period of time.

PRIORITY

Priority of queries in this resource pool when they compete for resources in the GENERAL pool:

PRIORITY { integer | HOLD }

• integer: Negative or positive integer value, where higher numbers denote higher priority:

  • User-defined resource pool: -100 to 100

  • Built-in resource pools SYSQUERY, RECOVERY, and TM: -110 to 110

• HOLD: Sets priority to -999. Queries in this resource pool are queued until QUEUETIMEOUT is reached.

Default: 0

QUEUETIMEOUT

Maximum time a request can wait for pool resources before it is rejected, not more than one year:

QUEUETIMEOUT { integer | 'interval' | 'NONE' }

• integer: Maximum wait time in seconds

• [interval](/en/sql-reference/language-elements/literals/datetime-literals/interval-literal/): Maximum wait time expressed in the following format:

  num year num months num [days] HH:MM:SS.ms
  

• NONE (empty string): No maximum wait time, request can be queued indefinitely, up to one year.

If the value that you specify resolves to more than one year, Vertica returns with a warning and sets the parameter to 365 days:

=> ALTER RESOURCE POOL user_0 QUEUETIMEOUT '11 months 50 days 08:32';
WARNING 5693:  Using 1 year for QUEUETIMEOUT
ALTER RESOURCE POOL
=> SELECT QUEUETIMEOUT FROM resource_pools WHERE name = 'user_0';
 QUEUETIMEOUT
--------------
 365
(1 row)

Default: 00:05 (5 minutes)

RUNTIMECAP

Maximum execution time allowed to queries in this resource pool, not more than one year, otherwise Vertica returns with an error. If a query exceeds this setting, it tries to cascade to a secondary pool:

RUNTIMECAP { 'interval' | NONE }

• interval: Maximum wait time expressed in the following format:

  num year num month num [day] HH:MM:SS.ms
  

• NONE (empty string): No maximum wait time, request can be queued indefinitely, up to one year.

If the user or session also has a RUNTIMECAP, the shorter limit applies.

RUNTIMEPRIORITY

Determines how the resource manager should prioritize dedication of run-time resources (CPU, I/O bandwidth) to queries already running in this resource pool:

RUNTIMEPRIORITY { HIGH | MEDIUM | LOW }

Default: MEDIUM

RUNTIMEPRIORITYTHRESHOLD

Maximum time (in seconds) in which query processing must complete before the resource manager assigns to it the resource pool's RUNTIMEPRIORITY. All queries begin execution with a priority of HIGH.

RUNTIMEPRIORITYTHRESHOLD seconds

Default: 2

SINGLEINITIATOR

Set to false for backward compatibility. Do not change this setting.

Privileges

Superuser

Examples

Set resource pool PRIORITY to 5:

=> ALTER RESOURCE POOL ceo_pool PRIORITY 5;

Designate a secondary resource pool:

=> CREATE RESOURCE POOL second_pool;
=> ALTER RESOURCE POOL ceo_pool CASCADE TO second_pool;

Decrease to 0% the MAXMEMORYSIZE and MEMORYSIZE settings on the dashboard subcluster's built-in TM resource pool. Changing these settings to 0 prevents the subcluster from running mergeout operations:

=> ALTER RESOURCE POOL TM FOR SUBCLUSTER dashboard MEMORYSIZE '0%'
   MAXMEMORYSIZE '0%';

See Tuning tuple mover pool settings for more information.

See also

• CREATE RESOURCE POOL
• DROP RESOURCE POOL
• CREATE USER
• RESOURCE_POOL_STATUS
• SET SESSION RESOURCE_POOL
• SET SESSION MEMORYCAP
• Managing workloads

21 - ALTER ROLE

Renames an existing role.

Syntax

ALTER ROLE name RENAME TO new-name

Parameters

name

The role to rename.

new-name

The role's new name.

Privileges

Superuser

Examples

=> ALTER ROLE applicationadministrator RENAME TO appadmin;
ALTER ROLE

See also

• CREATE ROLE
• DROP ROLE

22 - ALTER ROUTING RULE

Changes an existing load balancing policy routing rule.

Syntax

ALTER ROUTING RULE { rule_name | FOR WORKLOAD workload_name }  {
    RENAME TO new_name |
    SET ROUTE TO 'cidr_range'|
    SET GROUP TO group_name |
    SET WORKLOAD TO workload_name |
    SET SUBCLUSTER TO subcluster_name [,...] |
    SET PRIORITY TO priority
}

Parameters

rule_name

The name of the existing routing rule to change.

FOR WORKLOAD workload_name

The name of a workload.

RENAME TO new_name

The new name of the routing rule.

SET ROUTE TO 'cidr_range'

An IPv4 or IPv6 address range in CIDR format. Changes the address range of client connections this rule applies to.

SET GROUP TO group_name

The load balancing group that handles the connections that match this rule.

SET WORKLOAD TO workload_name

The name of the workload.

SET SUBCLUSTER TO subcluster_name

One or more subclusters to route clients to.

SET PRIORITY TO priority

Sets the priority for the routing rule, where priority is a non-negative INTEGER. The greater the value, the greater the priority. If a user or their role is granted multiple routing rules, the one with the greatest priority applies.

Examples

This example changes the routing rule named etl_rule so it uses the load balancing group named etl_rule to handle incoming connections in the IP address range of 10.20.100.0 to 10.20.100.255.

=> ALTER ROUTING RULE etl_rule SET GROUP TO etl_group;
ALTER ROUTING RULE
=> ALTER ROUTING RULE etl_rule SET ROUTE TO '10.20.100.0/24';
ALTER ROUTING RULE
=> \x
Expanded display is on.
=> SELECT * FROM routing_rules WHERE NAME = 'etl_rule';
-[ RECORD 1 ]----+---------------
name             | etl_rule
source_address   | 10.20.100.0/24
destination_name | etl_group

This example routes analytics workloads to the sc_analytics_2 subcluster:

=> ALTER ROUTING RULE FOR WORKLOAD analytics SET SUBCLUSTER TO `sc_analytics_2`;

This example changes the workload rule to handle reporting instead of analytics workloads:

=> ALTER ROUTING RULE FOR WORKLOAD analytics SET WORKLOAD TO reporting;

This example changes the priority of the workload rule for reporting workloads:

=> ALTER ROUTING RULE FOR WORKLOAD reporting SET PRIORITY TO 5;

See also

• CREATE ROUTING RULE
• DROP ROUTING RULE

23 - ALTER SCHEDULE

Modifies a schedule.

Syntax

ALTER SCHEDULE [[database.]schema.]schedule {
          OWNER TO new_owner
        | SET SCHEMA new_schema
        | RENAME TO new_schedule
        | USING CRON new_cron_expression
        | USING DATETIMES new_timestamp_list
    }

Parameters

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

schedule

The schedule to modify.

new_owner

The new owner of the schedule.

new_schema

The new schema of the schedule.

new_schedule

The new name for the schedule.

new_cron_expression

A cron expression. For example, to execute every day at 1:00PM:

=> ALTER SCHEDULE sched1 USING CRON '0 13 * * *';

new_timestamp_list

A comma-separated list of timestamps. For example, to execute at noon on October 2nd and November 2nd 2022:

=> ALTER SCHEDULE sched2 USING DATETIMES('2022-10-02 12:00:00', '2022-11-02 12:00:00');

Privileges

Superuser

Examples

To change the cron expression for a schedule:

=> ALTER SCHEDULE daily_schedule USING CRON '0 8 * * *';

To change a schedule that uses a cron expression to use a timestamp list instead:

=> ALTER SCHEDULE my_schedule USING DATETIMES('2023-10-01 12:30:00', '2022-11-01 12:30:00');

To rename a schedule:

=> ALTER SCHEDULE daily_schedule RENAME TO daily_8am_gmt;

24 - ALTER SCHEMA

Changes one or more schemas in one of the following ways:

• Enable or disable inheritance of schema privileges by tables created in the schemas.

• Reassign schema ownership to another user.

• Change schema disk quota.

• Rename one or more schemas.

Syntax

ALTER SCHEMA [{ namespace. | database. }]schema
    DEFAULT {INCLUDE | EXCLUDE} SCHEMA PRIVILEGES
    | OWNER TO user-name [CASCADE]
    | DISK_QUOTA { value | SET NULL }

You can rename more than one schema in a single operation:

ALTER SCHEMA [{ namespace. | database. }]schema[,...] RENAME TO [namespace.]new-schema-name[,...]

Parameters

namespace

For Eon Mode databases, namespace of the schema to alter. If no namespace is specified, the namespace of the schema is assumed to be default_namespace.

database

For Enterprise Mode databases, name of the database containing the schema. If specified, it must be the current database.

schema

Name of the schema to modify.

DEFAULT {INCLUDE | EXCLUDE} SCHEMA PRIVILEGES

Specifies whether to enable or disable default inheritance of privileges for new tables in the specified schema:

• EXCLUDE SCHEMA PRIVILEGES (default): Disables inheritance of schema privileges.

• INCLUDE SCHEMA PRIVILEGES: Specifies to grant tables in the specified schema the same privileges granted to that schema. This option has no effect on existing tables in the schema.

See also Enabling schema inheritance.

OWNER TO

Reassigns schema ownership to the specified user:

OWNER TO user-name [CASCADE]

By default, ownership of objects in the reassigned schema remain unchanged. To reassign ownership of schema objects to the new schema owner, qualify the OWNER TO clause with CASCADE. For details, see Cascading Schema Ownership below.

DISK_QUOTA

One of the following:

• A string, an integer followed by a supported unit: K, M, G, or T. If the new value is smaller than the current usage, the operation succeeds but no further disk space can be used until usage is reduced below the new quota.

• SET NULL to remove a quota.

For more information, see Disk quotas.

RENAME TO

Renames one or more schemas:

RENAME TO [namespace.]new-schema-name[,...]

The following requirements apply:

• The new schema name conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, models, and schemas in the namespace.

• If you specify multiple schemas to rename, the source and target lists must have the same number of names.

• If you specified the namespace of the schema to alter, you must provide the same namespace for the new-schema-name. You cannot rename a schema to a different namespace.

Caution

Renaming a schema referenced by a view causes the view to fail unless another schema is created to replace it.

Privileges

One of the following:

• Superuser

• Schema owner

Cascading schema ownership

By default, ALTER SCHEMA...OWNER TO does not affect ownership of objects in the target schema or the privileges granted on them. If you qualify the OWNER TO clause with CASCADE, Vertica acts as follows on objects in the target schema:

• Transfers ownership of objects owned by the previous schema owner to the new owner.

• Revokes all object privileges granted by the previous schema owner.

If issued by non-superusers, ALTER SCHEMA...OWNER TO CASCADE ignores all objects that belong to other users, and returns with notices on the objects that it cannot change. For example:

1. Schema ms is owned by user mayday, and contains two tables: ms.t1 owned by mayday, and ms.t2 owned by user joe:

   => \dt
                              List of tables
        Schema     |         Name          | Kind  |  Owner  | Comment
   ----------------+-----------------------+-------+---------+---------
    ms             | t1                    | table | mayday  |
    ms             | t2                    | table | joe     |
   

2. User mayday transfers ownership of schema ms to user dbadmin, using CASCADE. On return, ALTER SCHEMA reports that it cannot transfer ownership of table ms.t2 and its projections, which are owned by user joe:

   
   => \c - mayday
   You are now connected as user "mayday".
   => ALTER SCHEMA ms OWNER TO dbadmin CASCADE;
   NOTICE 3583:  Insufficient privileges on ms.t2
   NOTICE 3583:  Insufficient privileges on ms.t2_b0
   NOTICE 3583:  Insufficient privileges on ms.t2_b1
   ALTER SCHEMA
   => \c
   You are now connected as user "dbadmin".
   => \dt
                              List of tables
        Schema     |         Name          | Kind  |  Owner  | Comment
   ----------------+-----------------------+-------+---------+---------
    ms             | t1                    | table | dbadmin |
    ms             | t2                    | table | joe     |
   

3. User dbadmin transfers ownership of schema ms to user pat, again using CASCADE. This time, because dbadmin is a superuser, ALTER SCHEMA transfers ownership of all ms tables to user pat

   => ALTER SCHEMA ms OWNER TO pat CASCADE;
   ALTER SCHEMA
   => \dt
                              List of tables
        Schema     |         Name          | Kind  |  Owner  | Comment
   ----------------+-----------------------+-------+---------+---------
    ms             | t1                    | table | pat     |
    ms             | t2                    | table | pat     |
   

Swapping schemas

Renaming schemas is useful for swapping schemas without actually moving data. To facilitate the swap, enter a non-existent, temporary placeholder schema. For example, the following ALTER SCHEMA statement uses the temporary schema temps to facilitate swapping schema S1 with schema S2. In this example, S1 is renamed to temps. Then S2 is renamed to S1. Finally, temps is renamed to S2.

=> ALTER SCHEMA S1, S2, temps RENAME TO temps, S1, S2;

Examples

The following example renames schemas S1 and S2 to S3 and S4, respectively:

=> ALTER SCHEMA S1, S2 RENAME TO S3, S4;

This example sets the default behavior for new table t2 to automatically inherit the schema's privileges:

=> ALTER SCHEMA s1 DEFAULT INCLUDE SCHEMA PRIVILEGES;

=> CREATE TABLE s1.t2 (i, int);

This example sets the default for new tables to not automatically inherit privileges from the schema:

=> ALTER SCHEMA s1 DEFAULT EXCLUDE SCHEMA PRIVILEGES;

In an Eon Mode database, the following statement renames the train schema to ferry in the transit namespace:

=> ALTER SCHEMA transit.train RENAME TO transit.ferry;

See also

• CREATE SCHEMA
• DROP SCHEMA

25 - ALTER SEQUENCE

Changes a sequence in two ways:

• Changes values that control sequence behavior—for example, its start value and range of minimum and maximum values. These changes take effect only when you start a new database session.
• Changes sequence name, schema, or ownership. These changes take effect immediately.

Syntax

Change sequence behavior:

ALTER SEQUENCE [[database.]schema.]sequence
    [ INCREMENT [ BY ] integer ]
    [ MINVALUE integer | NO MINVALUE ]
    [ MAXVALUE integer | NO MAXVALUE ]
    [ RESTART [ WITH ] integer ]
    [ CACHE integer | NO CACHE ]
    [ CYCLE | NO CYCLE ]

Change sequence name, schema, or ownership:

ALTER SEQUENCE [schema.]sequence-name {
    RENAME TO name
    | SET SCHEMA schema]
    | OWNER TO owner
}

Arguments

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

sequence

Name of the sequence to alter.

In the case of IDENTITY table columns, Vertica generates the sequence name using the following convention:

table-name_col-name_seq

To obtain this name, query the SEQUENCES system table.

INCREMENT integer

Positive or negative minimum value change on each call to NEXTVAL. The default is 1.

This value is a minimum increment size. The sequence can increment by more than this value unless you also specify NO CACHE.

MINVALUE integer | NO MINVALUE

Minimum value the sequence can generate. If this change would invalidate the current sequence position, the operation fails.

MAXVALUE integer | NO MAXVALUE

Maximum value the sequence can generate. If this change would invalidate the current sequence position, the operation fails.

RESTART integer

New start value of the sequence. The next call to NEXTVAL returns the new start value.

Caution

Using ALTER SEQUENCE to set a sequence start value below its current value can result in duplicate keys.

CACHE | NO CACHE

How many unique sequence numbers to pre-allocate and store in memory on each node for faster access. Vertica sets up caching for each session and distributes it across all nodes. A value of 0 or 1 is equivalent to NO CACHE.

Caution

If sequence caching is set to a low number, nodes might request a new set of cache values more frequently. While it supplies a new cache, Vertica must lock the catalog. Until Vertica releases the lock, other database activities such as table inserts are blocked, which can adversely affect overall performance.

By default, the sequence cache is set to 250,000.

For details, see Distributing sequences.

CYCLE | NO CYCLE

How to handle reaching the end of the sequence. The default is NO CYCLE, meaning that a call to NEXTVAL returns an error after the sequence reaches its upper or lower limit. CYCLE instead wraps, as follows:

• Ascending sequence: wraps to the minimum value.
• Descending sequence: wraps to the maximum value.

RENAME TO name

New name in the current schema for the sequence. Supported only for named sequences.

SET SCHEMA schema

Moves the sequence to a new schema. Supported only for named sequences.

OWNER TO owner

Reassigns sequence ownership to another user.

Privileges

For named sequences, USAGE on the schema and one of the following:

• Sequence owner

• ALTER privilege on the sequence

• For certain operations, non-superusers must have the following schema privileges:

  • To rename a sequence: CREATE, USAGE
  • To move a sequence to another schema: CREATE on the destination, USAGE on the source

For IDENTITY column sequences, USAGE on the table schema and one of the following:

• Table owner

• ALTER privileges

Non-superusers must also have SELECT privileges to enable or disable constraint enforcement, or remove partitioning.

Examples

See Altering sequences.

See also

26 - ALTER SESSION

ALTER SESSION sets and clears session-level configuration parameter values for the current session. To identify session-level parameters, query system table CONFIGURATION_PARAMETERS.

Syntax

ALTER SESSION {
    SET [PARAMETER] parameter-name=value[,...]
    | CLEAR { [PARAMETER] parameter-name[,...] | PARAMETER ALL }
    | SET UDPARAMETER [ FOR libname ] key=value[,...]
    | CLEAR UDPARAMETER { [ FOR libname ] key[,...] | ALL }
}

Parameters

SET [PARAMETER]

Sets one or more configuration parameters to the specified value.

CLEAR [PARAMETER]

Clears the specified configuration parameters of changes that were set in the current session.

CLEAR PARAMETER ALL

Clears all session-level configuration parameters of changes that were set in the current session.

SET UDPARAMETER

Sets one or more user-defined session parameters (key=value) to be used with a UDx. Key value sizes are restricted as follows:

• Set from client side: 128 characters

• Set from UDx side: unlimited

You can limit the SET operation's scope to a single library by including the clause FOR libname. For example:

=> ALTER SESSION SET UDPARAMETER FOR securelib username='alice';

If you specify a library, then only that library can access the parameter's value. Use this restriction to protect parameters that hold sensitive data, such as credentials.

CLEAR UDPARAMETER

Clears user-defined parameters, specified by one of the following options:

• [FOR libname] key[,...]: Clears the key-specified parameters, optionally scoped to library libname.

• ALL: Clears all user-defined parameters in the current session.

Privileges

None

Examples

Set and clear a parameter

• Force all UDxes that support fenced mode to run in fenced mode, even if their definition specifies NOT FENCED:

  => ALTER SESSION SET ForceUDxFencedMode = 1;
  ALTER SESSION
  

• Clear ForceUDxFencedMode at the session level. Its value is reset to its default value 0:

  => ALTER SESSION CLEAR ForceUDxFencedMode;
  ALTER SESSION
  => SELECT parameter_name, current_value, default_value FROM configuration_parameters WHERE  parameter_name = 'ForceUDxFencedMode';
     parameter_name   | current_value | default_value
  --------------------+---------------+---------------
   ForceUDxFencedMode | 0             | 0
  (1 row)
  

• Clear all session-level configuration parameters of changes that were set in this session:

  => ALTER SESSION CLEAR PARAMETER ALL;
  ALTER SESSION
  

Set and clear a user-defined parameter

• Set the value of user-defined parameter RowCount in library MyLibrary to 25.

  => ALTER SESSION SET UDPARAMETER FOR MyLibrary RowCount = 25;
  ALTER SESSION
  

• Clear RowCount at the session level:

  => ALTER SESSION CLEAR UDPARAMETER FOR MyLibrary RowCount;
  ALTER SESSION
  

27 - ALTER SUBCLUSTER

Changes the configuration of a subcluster. You can use this statement to rename a subcluster or make it the default subcluster.

Syntax

ALTER SUBCLUSTER subcluster-name {
    RENAME TO new-name |
    SET DEFAULT
}

Parameters

subcluster-name

The name of the subcluster to alter.

RENAME TO new-name

Changes the name of the subcluster to new-name.

SET DEFAULT

Makes the subcluster the default subcluster. When you add new nodes to the database and do not specify a subcluster to contain them, Vertica adds them to the default subcluster. There can be only one default subcluster at a time. The subcluster that was previously the default subcluster becomes a non-default subcluster.

Privileges

Examples

This example makes the analytics_cluster the default subcluster:

=> SELECT DISTINCT subcluster_name FROM SUBCLUSTERS WHERE is_default = true;
  subcluster_name
--------------------
 default_subcluster
(1 row)

=> ALTER SUBCLUSTER analytics_cluster SET DEFAULT;
ALTER SUBCLUSTER
=> SELECT DISTINCT subcluster_name FROM SUBCLUSTERS WHERE is_default = true;
  subcluster_name
-------------------
 analytics_cluster
(1 row)

This example renames default_subcluster to load_subcluster:

=> ALTER SUBCLUSTER default_subcluster RENAME TO load_subcluster;
ALTER SUBCLUSTER

=> SELECT DISTINCT subcluster_name FROM subclusters;
  subcluster_name
-------------------
 load_subcluster
 analytics_cluster
(2 rows)

See also

• CRITICAL_SUBCLUSTERS
• DEMOTE_SUBCLUSTER_TO_SECONDARY
• PROMOTE_SUBCLUSTER_TO_PRIMARY
• SHUTDOWN_SUBCLUSTER
• SUBCLUSTERS

28 - ALTER SUBNET

Renames an existing subnet.

Syntax

ALTER SUBNET subnet-name RENAME TO new-subnet-name

Parameters

subnet-name

The name of the existing subnet.

new-subnet-name

The new name for the subnet.

Privileges

Superuser

Examples

=> ALTER SUBNET mysubnet RENAME TO myNewSubnet;

29 - ALTER TABLE

Modifies the metadata of an existing table. All changes are auto-committed.

Syntax

ALTER TABLE [[{ namespace. | database. } ]schema.]table {
    ADD COLUMN [ IF NOT EXISTS ] column datatype
       [ column‑constraint ]
       [ ENCODING encoding-type ]
       [ PROJECTIONS (projections-list) | ALL PROJECTIONS ]
       [, ...]
    | ADD table-constraint
    | ALTER COLUMN column {
        ENCODING encoding-type PROJECTIONS (projection-list)
        | { SET | DROP } expression }
    | ALTER CONSTRAINT constraint-name { ENABLED | DISABLED }
    | DISK_QUOTA { value | SET NULL }
    | DROP CONSTRAINT constraint-name [ CASCADE | RESTRICT ]
    | DROP [ COLUMN ] [ IF EXISTS ] column [ CASCADE | RESTRICT ]
    | FORCE OUTER integer
    | { INCLUDE | EXCLUDE | MATERIALIZE } [ SCHEMA ] PRIVILEGES
    | OWNER TO owner
    | partition-clause [ REORGANIZE ]
    | REMOVE PARTITIONING
    | RENAME [ COLUMN ] name TO new‑name
    | RENAME TO new-table-name[,...]
    | REORGANIZE
    | SET {
        ActivePartitionCount { count | DEFAULT }
        | IMMUTABLE ROWS
        | MERGEOUT { 1 | 0 }
        | SCHEMA [namespace.]schema }
}

=> ALTER TABLE my_table OWNER TO Joe,
   ADD CONSTRAINT unique_b UNIQUE (b) ENABLED;

Arguments

namespace

For Eon Mode databases, namespace that contains the table to alter. If no namespace is specified, the namespace of the table is assumed to be default_namespace.

database

For Enterprise Mode databases, name of the database containing the table. If specified, it must be the current database.

schema

Name of the schema that contains the table to alter.

table

The table to alter.

ADD COLUMN

Adds one or more columns to the table and, by default, to all its superprojections:

ADD COLUMN [IF NOT EXISTS] column datatype 
    [ NULL | NOT NULL ]
    [ ENCODING encoding‑type]
    [ PROJECTIONS (projections-list) | ALL PROJECTIONS]

Restrictions on columns of complex types also apply to columns that you add using ADD COLUMN.

Adding a column with the same name as one that already exists is an error unless you use IF NOT EXISTS.

You can qualify the new column definition with one of these options:

• A column constraint:

    [ CONSTRAINT constraint-name ]
    { [NOT] NULL 
      | DEFAULT expression 
      | SET USING expression 
      | DEFAULT USING expression }
  

  Columns that use DEFAULT with static values can be added in a single ALTER TABLE statement. Columns that use non-static DEFAULT values must be added in separate ALTER TABLE statements.

• ENCODING specifies the column's encoding type, by default set to AUTO.

• PROJECTIONS adds the new column to one or more existing projections of this table, specified as a comma-delimted list of projection base names. Vertica adds the column to all buddies of each projection. The projection list cannot include projections with pre-aggregated data such as live aggregate projections; otherwise, Vertica rolls back the ALTER TABLE statement.

• ALL PROJECTIONS adds the column to all projections of this table, excluding projections with pre-aggregated data.

ADD table‑constraint

Adds a constraint to a table that does not have any associated projections.

ALTER COLUMN

You can alter an existing column in one of two ways:

• Set encoding on a column for one or more projections of this table:

  ENCODING encoding-type PROJECTIONS (projections-list)
  

  where projections-list is a comma-delimited list of projections to update with the new encoding. You can specify each projection in two ways:

  • Projection base name: Update all projections that share this base name.

  • Projection name: Update the specified projection. If the projection is segmented, the change is propagated to all buddies.

  If one of the projections does not contain the target column, Vertica returns with a rollback error.

  For details, see Projection Column Encoding.

• Set or drop a setting for a column of scalar data, including primitive arrays:

  SET { DEFAULT expression
            | USING expression
      | DEFAULT USING expression
      | NOT NULL
      | DATA TYPE datatype
  }
  DROP { DEFAULT
       | SET USING
       | DEFAULT USING
       | NOT NULL
  }
  

  You cannot change the data type of a column of any complex type that is neither a scalar type nor an array of scalar types. One exception applies: in external tables, you can change a primitive column type to a complex type.

  Setting a DEFAULT or SET USING expression has no effect on existing column values. To refresh the column with its DEFAULT or SET USING expression, update it as follows

  • SET USING column: Call REFRESH_COLUMNS on the table.

  • DEFAULT column: update the column as follows:

    UPDATE table-name SET column-name=DEFAULT;
    

Altering a column with DEFAULT or SET USING can increase disk usage, which can cause the operation to fail if it would violate the table or schema disk quota.

ALTER CONSTRAINT

Specifies whether to enforce primary key, unique key, and check constraints:

ALTER CONSTRAINT constraint-name {ENABLED | DISABLED}

DISK_QUOTA

One of the following:

• A string, an integer followed by a supported unit: K, M, G, or T. If the new value is smaller than the current usage, the operation succeeds but no further disk space can be used until usage is reduced below the new quota.

• SET NULL to remove a quota.

For more information, see Disk quotas.

DROP CONSTRAINT

Drops the specified table constraint from the table:

DROP CONSTRAINT constraint-name [CASCADE | RESTRICT]

You can qualify DROP CONSTRAINT with one of these options:

• CASCADE: Drops a constraint and all dependencies in other tables.

• RESTRICT: Does not drop a constraint if there are dependent objects. Same as the default behavior.

Dropping a table constraint has no effect on views that reference the table.

DROP [COLUMN]

Drops the specified column from the table and that column's ROS containers:

DROP [COLUMN] [IF EXISTS] column [CASCADE | RESTRICT]

You can qualify DROP COLUMN with one of these options:

• IF EXISTS generates an informational message if the column does not exist. If you omit this option and the column does not exist, Vertica generates a ROLLBACK error message.

• CASCADE is required if the column has dependencies.

• RESTRICT drops the column only from the given table.

The column's table cannot be immutable.

See Dropping table columns.

FORCE OUTER integer

Specifies whether a table is joined to another as an inner or outer input. For details, see Controlling join inputs.

{INCLUDE | EXCLUDE | MATERIALIZE} [SCHEMA] PRIVILEGES

Specifies default inheritance of schema privileges for this table:

• EXCLUDE PRIVILEGES (default) disables inheritance of privileges from the schema.

• INCLUDE PRIVILEGES grants the table the same privileges granted to its schema.

• MATERIALIZE PRIVILEGES copies grants to the table and creates a GRANT object on the table. This disables the inherited privileges flag on the table, so you can:

  • Grant more specific privileges at the table level.

  • Use schema-level privileges as a template.

  • Move the table to a different schema.

  • Change schema privileges without affecting the table.

  Note

  If inherited privileges are disabled at the database level, schema privileges can still be materialized.

See also Setting privilege inheritance on tables and views.

OWNER TO owner

Changes the table owner.

partition-clause [REORGANIZE]

Invalid for external tables, logically divides table data storage through a PARTITION BY clause:

PARTITION BY expression
  [ GROUP BY expression ]
  [ SET ACTIVEPARTITIONCOUNT integer ]

For details, see Partition clause.

If you qualify the partition clause with REORGANIZE and the table previously specified no partitioning, the Vertica Tuple Mover immediately implements the partition clause. If the table previously specified partitioning, the Tuple Mover evaluates ROS storage containers and reorganizes them as needed to conform with the new partition clause.

REMOVE PARTITIONING

Specifies to remove partitioning from a table definition. The Tuple Mover subsequently removes existing partitions from ROS containers.

RENAME [COLUMN]

Renames the specified column within the table. The column's table cannot be immutable.

RENAME TO

Renames one or more tables:

RENAME TO new-table-name[,...]

The following requirements apply:

• The renamed table must be in the same schema as the original table.

• The new table name conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.

• If you specify multiple tables to rename, the source and target lists must have the same number of names.

REORGANIZE

Valid only for partitioned tables, invokes the Tuple Mover to reorganize ROS storage containers as needed to conform with the table's current partition clause. ALTER TABLE...REORGANIZE and Vertica meta-function PARTITION_TABLE operate identically.

REORGANIZE can also qualify a new partition clause.

SET

Changes a table setting, one of the following:

• ActivePartitionCount { count | DEFAULT }, valid only for partitioned tables, specifies how many partitions are active for this table, one of the following:

  • count: Unsigned integer, supersedes configuration parameter ActivePartitionCount.

  • DEFAULT: Removes the table-level active partition count. The table obtains its active partition count from the configuration parameter ActivePartitionCount.

  For details on usage, see Active and inactive partitions.

• IMMUTABLE ROWS prevents changes to table row values by blocking DML operations such as UPDATE and DELETE. Once set, table immutability cannot be reverted.

  You cannot set a flattened table to be immutable. For details on all immutable table restrictions, see Immutable tables.

• MERGEOUT { 1 | 0 } specifies whether to enable or disable mergeout to ROS containers that consolidate projection data of this table. By default, mergeout is enabled (1) on all tables.

• SCHEMA [namespace.]schema-name moves the table from its current schema to [namespace.]schema-name. For Eon Mode databases, the new schema must be in the same namespace as the old schema. When no namespace is provided, the namespace is set to default_namespace.

Vertica automatically moves all projections that are anchored to the source table to the destination schema. It also moves all IDENTITY columns to the destination schema. For details, see Moving tables to another schema

Privileges

Non-superuser: USAGE on the schema and one of the following:

• Table owner

• ALTER privileges

Non-superusers must also have SELECT privileges to enable or disable constraint enforcement, or remove partitioning.

For certain operations, non-superusers must have the following schema privileges:

• To rename a table: CREATE, USAGE

• To move a table to another schema: USAGE on the source schema, CREATE on the destination schema

Restrictions for complex types

Complex types used in native tables have some restrictions, in addition to the restrictions for individual types listed on their reference pages:

• A native table must have at least one column that is a primitive type or a native array (one-dimensional array of a primitive type). If a flex table has real columns, it must also have at least one column satisfying this restriction.

• Complex type columns cannot be used in ORDER BY or PARTITION BY clauses nor as FILLER columns.

• Complex type columns cannot have constraints.

• Complex type columns cannot use DEFAULT or SET USING.

• Expressions returning complex types cannot be used as projection columns, and projections cannot be segmented or ordered by columns of complex types.

Exclusive ALTER TABLE clauses

The following ALTER TABLE clauses cannot be combined with another ALTER TABLE clause:

• ADD COLUMN

• DROP COLUMN

• RENAME COLUMN

• SET SCHEMA

• RENAME [TO]

Node down limitations

Enterprise Mode only

The following ALTER TABLE operations are not supported when one or more database cluster nodes are down:

• ALTER COLUMN ... ADD table-constraint

• ALTER COLUMN ... SET DATA TYPE

• ALTER COLUMN ... { SET DEFAULT | DROP DEFAULT }

• ALTER COLUMN ... { SET USING | DROP SET USING }

• ALTER CONSTRAINT

• DROP COLUMN

• DROP CONSTRAINT

Pre-aggregated projection restrictions

You cannot modify the metadata of anchor table columns that are included in live aggregate or Top-K projections. You also cannot drop these columns. To make these changes, you must first drop all live aggregate and Top-K projections that are associated with it.

External table restrictions

Not all ALTER TABLE options pertain to external tables. For instance, you cannot add a column to an external table, but you can rename the table:

=> ALTER TABLE mytable RENAME TO mytable2;
ALTER TABLE

Locked tables

If the operation cannot obtain an O lock on the target table, Vertica tries to close any internal Tuple Mover sessions that are running on that table. If successful, the operation can proceed. Explicit Tuple Mover operations that are running in user sessions do not close. If an explicit Tuple Mover operation is running on the table, the operation proceeds only when the operation is complete.

Examples

In an Eon Mode database, the following statement alters the schema of the flights table from schema airline1 in namespace airport to schema airline2 in namespace airport:

=> ALTER TABLE airport.airline1.flights SET SCHEMA airpot.airline2.flights;

See also

• Working with native tables
• Altering table definitions
• Adding table columns

29.1 - Projection column encoding

After you create a table and its projections, you can call ALTER TABLE...ALTER COLUMN to set or change the encoding type of an existing column in one or more projections. For example:

ALTER TABLE store.store_dimension ALTER COLUMN store_region
  ENCODING rle PROJECTIONS (store.store_dimension_p1_b0, store.store_dimension_p2);

In this example, the ALTER TABLE statement specifies to set RLE encoding on column store_region for two projections: store_dimension_p1_b0 and store_dimension_p2. The PROJECTIONS list references the two projections by their projection name and base name, respectively. You can reference a projection either way; in both cases, the change is propagated to all buddies of the projection and stored in its DDL accordingly:

=> select export_objects('','store.store_dimension');

                          export_objects
------------------------------------------------------------------
CREATE TABLE store.store_dimension
(
    store_key int NOT NULL,
    store_name varchar(64),
    store_number int,
    store_address varchar(256),
    store_city varchar(64),
    store_state char(2),
    store_region varchar(64)
);

CREATE PROJECTION store.store_dimension_p1
(
 store_key,
 store_name,
 store_number,
 store_address,
 store_city,
 store_state,
 store_region ENCODING RLE
)
AS
 SELECT store_dimension.store_key,
        store_dimension.store_name,
        store_dimension.store_number,
        store_dimension.store_address,
        store_dimension.store_city,
        store_dimension.store_state,
        store_dimension.store_region
 FROM store.store_dimension
 ORDER BY store_dimension.store_key
SEGMENTED BY hash(store_dimension.store_key) ALL NODES KSAFE 1;

CREATE PROJECTION store.store_dimension_p2
(
 store_key,
 store_name,
 store_number,
 store_address,
 store_city,
 store_state,
 store_region ENCODING RLE
)
AS
 SELECT ...

30 - ALTER TLS CONFIGURATION

Alters a specified TLS Configuration object. For information on existing TLS Configuration objects, query TLS_CONFIGURATIONS.

Syntax

ALTER TLS CONFIGURATION tls_config_name {
    [ CERTIFICATE { NULL | cert_name } ]
    [ ADD CA CERTIFICATES ca_cert_name [,...] ]
    [ REMOVE CA CERTIFICATES ca_cert_name [,...] ]
    [ CIPHER SUITES { '' | 'openssl_cipher [,...]' } ]
    [ TLSMODE 'tlsmode' ]
    [ OWNER TO user_name ]
}

Parameters

tls_config_name

The TLS Configuration object to alter.

NULL

Removes the non-CA certificate from the TLS Configuration.

cert_name

A certificate created with CREATE CERTIFICATE.

You must have USAGE privileges on the certificate (either from ownership of the certificate or USAGE on its key, if any) to add it to a TLS Configuration.

ca_cert_name

A CA certificate created with CREATE CERTIFICATE.

You must have USAGE privileges on the certificate (either from ownership of the certificate or USAGE on its key, if any) to add it to a TLS Configuration.

openssl_cipher

A comma-separated list of cipher suites to use instead of the default set of cipher suites. Providing an empty string for this parameter clears the alternate cipher suite list and instructs the specified TLS Configuration to use the default set of cipher suites.

To view enabled cipher suites, use LIST_ENABLED_CIPHERS.

tlsmode

How Vertica establishes TLS connections and handles certificates, one of the following, in order of ascending security:

• DISABLE: Disables TLS. All other options for this parameter enable TLS.

• ENABLE: Enables TLS. Vertica does not check client certificates.

• TRY_VERIFY: Establishes a TLS connection if one of the following is true:

  • the other host presents a valid certificate

  • the other host doesn't present a certificate

  If the other host presents an invalid certificate, the connection will use plaintext.

• VERIFY_CA: Connection succeeds if Vertica verifies that the other host's certificate is from a trusted CA. If the other host does not present a certificate, the connection uses plaintext.

• VERIFY_FULL: Connection succeeds if Vertica verifies that the other host's certificate is from a trusted CA and the certificate's cn (Common Name) or subjectAltName attribute matches the hostname or IP address of the other host.

  Note that for client certificates, cn is used for the username, so subjectAltName must match the hostname or IP address of the other host.

VERIFY_FULL is unsupported for client-server TLS (the connection type handled by ServerTLSConfig) and behaves like VERIFY_CA.

Note

Whether Vertica or the other party acts as the client or server depends on the type of connection. For connections between the Vertica database and an LDAP server for LDAP Link or LDAP authentication, the Vertica database is the client and the LDAP server is the server:

• LDAPLinkTLSConfig

• LDAPAuthTLSConfig

For all other connection types, Vertica is the server and the other party is the client:

• ServerTLSConfig

• InternodeTLSConfig (the other Vertica nodes are both the client and server)

Privileges

Non-superuser: ALTER privileges on the TLS Configuration.

Examples

To remove all certificates and CA certificates from the LDAPLink TLS Configuration:

=>  SELECT * FROM tls_configurations WHERE name='LDAPLink';
   name   |  owner  | certificate | ca_certificate | cipher_suites |  mode
----------+---------+-------------+----------------+---------------+---------
 LDAPLink | dbadmin | server_cert | ca             |               | DISABLE
 LDAPLink | dbadmin | server_cert | ica            |               | DISABLE
(2 rows)

=> ALTER TLS CONFIGURATION LDAPLink CERTIFICATE NULL REMOVE CA CERTIFICATES ca, ica;
ALTER TLS CONFIGURATION

=> SELECT * FROM tls_configurations WHERE name='LDAPLink';
   name   |  owner  | certificate | ca_certificate | cipher_suites |  mode
----------+---------+-------------+----------------+---------------+---------
 LDAPLink | dbadmin |             |                |               | DISABLE
(3 rows)

To use an alternate set of cipher suites for client-server TLS:

 => ALTER TLS CONFIGURATION server CIPHER SUITES
    'DHE-PSK-AES256-CBC-SHA384,
     DHE-PSK-AES128-GCM-SHA256,
     PSK-AES128-CBC-SHA256';
ALTER TLS CONFIGURATION

 => SELECT name, cipher_suites FROM tls_configurations WHERE name='server';
   name   |                               cipher_suites
 server   | DHE-PSK-AES256-CBC-SHA384,DHE-PSK-AES128-GCM-SHA256,PSK-AES128-CBC-SHA256
(1 row)

For other examples, see:

• Configuring client-server TLS
• TLS for LDAP link
• TLS for LDAP authentication
• Internode TLS

31 - ALTER TRIGGER

Modifies a trigger.

Syntax

ALTER TRIGGER [[database.]schema.]trigger {
          OWNER TO new_owner
        | SET SCHEMA new_schema
        | RENAME TO new_trigger
        | PROCEDURE TO new_procedure
    }

Parameters

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

trigger

The trigger to modify.

new_owner

The new owner and definer of the trigger. This affects the behavior of AS DEFINER.

new_schema

The new schema of the trigger.

new_trigger

The new name for the trigger.

new_procedure

The function signature of the stored procedure.

Privileges

Superuser

Examples

To attach a different procedure to the trigger:

=> ALTER TRIGGER daily_1am PROCEDURE TO log_user_actions(10, 20);

To rename a trigger:

=> ALTER TRIGGER daily_1am RENAME TO daily_1am_gmt;

To move the trigger to a different schema:

=> ALTER TRIGGER daily_1am_gmt

32 - ALTER USER

Changes user account parameters and user-level configuration parameters.

Syntax

ALTER USER user-name {
   account-parameter value[,...]
   | SET [PARAMETER] cfg-parameter=value[,...]
   | CLEAR [PARAMETER] cfg-parameter[,...]
}

Parameters

user-name

Name of the user. Names that contain special characters must be double-quoted. To enforce case-sensitivity, use double-quotes.

For details on name requirements, see Creating a database name and password.

account-parameter value

Specifies user account settings (see below).

Note

Changes to a user account apply only to the current session and to all later sessions launched by this user.

SET [PARAMETER]

Sets the specified configuration parameters. The new setting applies only to the current session, and to all later sessions launched by this user. Concurrent user sessions are unaffected by new settings unless they call meta-function RESET_SESSION.

CLEAR [PARAMETER]

Resets the specified configuration parameters to their default values.

User account parameters

Specify one or more user-account parameters and their settings as a comma-delimited list:

account-parameter value[,...]

IDENTIFIED BY '[new-password]'
   | ['hashed-password' SALT 'hash-salt'] 
   [REPLACE 'current-password']

Privileges

Non-superusers can change the following options on their own user accounts:

• IDENTIFIED BY

• RESOURCE POOL

• SEARCH_PATH

• SECURITY_ALGORITHM

When changing a another user's resource pool to one outside of the PUBLIC schema, the user must have USAGE privileges on the resource pool from at least one of the following:

• Object ownership

• Explicit grant to the user

• Default role

Setting user-level configuration parameters

SET | CLEAR PARAMETER can specify only user-level configuration parameters, otherwise Vertica returns an error. Only superusers can set and clear user-level parameters, unless they are also supported at the session level.

To get the names of user-level parameters, query system table CONFIGURATION_PARAMETERS. For example:

=> SELECT parameter_name, allowed_levels FROM configuration_parameters
      WHERE allowed_levels ilike '%USER%' AND parameter_name ilike '%depot%' ORDER BY parameter_name;
       parameter_name        |     allowed_levels
-----------------------------+-------------------------
 BackgroundDepotWarming      | SESSION, USER, DATABASE
 DepotOperationsForQuery     | SESSION, USER, DATABASE
 EnableDepotWarmingFromPeers | SESSION, USER, DATABASE
 UseDepotForReads            | SESSION, USER, DATABASE
 UseDepotForWrites           | SESSION, USER, DATABASE
(5 rows)

The following example sets the user-level configuration parameter UseDepotForWrites for two users, Yvonne and Ahmed:

=> SHOW USER Yvonne PARAMETER ALL;
  user  |        parameter        | setting
--------+-------------------------+---------
 Yvonne | DepotOperationsForQuery | Fetches
(1 row)

=> ALTER USER Yvonne SET PARAMETER UseDepotForWrites = 0;
ALTER USER
=> SHOW USER Yvonne PARAMETER ALL;
  user  |        parameter        | setting
--------+-------------------------+---------
 Yvonne | DepotOperationsForQuery | Fetches
 Yvonne | UseDepotForWrites       | 0
(2 rows)

=> ALTER USER Ahmed SET PARAMETER DepotOperationsForQuery = 'Fetches';
ALTER USER
=> SHOW USER ALL PARAMETER ALL;
  user  |        parameter        | setting
--------+-------------------------+---------
 Ahmed  | DepotOperationsForQuery | Fetches
 Yvonne | DepotOperationsForQuery | Fetches
 Yvonne | UseDepotForWrites       | 0
(3 rows)

Examples

Set a user's password

=> CREATE USER user1;
=> ALTER USER user1 IDENTIFIED BY 'newpassword';

Set user's security algorithm and password

This example sets a user's security algorithm and password to SHA-512 and newpassword, respectively. When you execute the ALTER USER statement, Vertica hashes the password with the SHA-512 algorithm and saves the hash:

=> CREATE USER user1;
        => ALTER USER user1 SECURITY_ALGORITHM 'SHA512' IDENTIFIED BY 'newpassword'

Assign default roles to a user

This example make a user's assigned roles their default roles. Default roles are automatically set (enabled) when a user logs in:

=> CREATE USER user1;
CREATE USER
=> GRANT role1, role2, role3 to user1;
=> ALTER USER user1 DEFAULT ROLE ALL;

You can pair ALL with EXCEPT to exclude certain roles:

=> CREATE USER user2;
CREATE USER
=> GRANT role1, role2, role3 to user2;
=> ALTER USER user2 DEFAULT ROLE ALL EXCEPT role1;

See also

• CREATE USER
• DROP USER
• SHOW USER

33 - ALTER VIEW

Modifies the metadata of an existing view. The changes are auto-committed.

Syntax

General usage:

ALTER VIEW [[database.]schema.]view {
    | OWNER TO owner
    | SET SCHEMA schema
    | { INCLUDE | EXCLUDE | MATERIALIZE } [ SCHEMA ] PRIVILEGES
}

Rename view:

ALTER VIEW [[database.]schema.]view[,...] RENAME TO new-view-name[,...]

Parameters

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

view

The view to alter.

SET SCHEMA schema``

Moves the view from one schema to another.

OWNER TO owner

Changes the view owner.

Important

The new view owner should also have SELECT privileges on the objects that the view references; otherwise the view is inaccessible to that user.

{ INCLUDE | EXCLUDE | MATERIALIZE } [SCHEMA] PRIVILEGES

Specifies default inheritance of schema privileges for this view:

• EXCLUDE [SCHEMA] PRIVILEGES (default) disables inheritance of privileges from the schema.

• INCLUDE [SCHEMA] PRIVILEGES grants the view the same privileges granted to its schema.

• MATERIALIZE: Copies grants to the view and creates a GRANT object on the view. This disables the inherited privileges flag on the view, so you can:

  • Grant more specific privileges at the view level

  • Use schema-level privileges as a template

  • Move the view to a different schema

  • Change schema privileges without affecting the view

  Note

  If inherited privileges are disabled at the database level, schema privileges can still be materialized.

See also Setting privilege inheritance on tables and views.

RENAME TO

Renames one or more views:

RENAME TO new-view-name[,...]

The following requirements apply:

• The new view name conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.

• If you specify multiple views to rename, the source and target lists must have the same number of names.

• Renaming a view requires USAGE and CREATE privileges on the schema that contains the view.

Privileges

Non-superuser: USAGE on the schema and one of the following:

• View owner

• ALTER privilege on the view

For certain operations, non-superusers must have the following schema privileges:

Examples

Rename view view1 to view2:

=> CREATE VIEW view1 AS SELECT * FROM t;
CREATE VIEW
=> ALTER VIEW view1 RENAME TO view2;
ALTER VIEW