This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

DROP statements

DROP statements let you delete database objects such as schemas, tables, and users.

DROP statements let you delete database objects such as schemas, tables, and users.

1 - DROP ACCESS POLICY

Removes an access policy from a column or row.

Removes an access policy from a column or row.

Syntax

DROP ACCESS POLICY ON table FOR { COLUMN column | ROWS}

Parameters

table
Name of the table that contains the column access policy to remove
column
Name of the column that contains the access policy to remove

Privileges

Non-superuser: Ownership of the table

Examples

These examples show various cases where you can drop an access policy.

Drop column access policy:

=> DROP ACCESS POLICY ON customer FOR COLUMN Customer_Number;

Drop row access policy on a table:

=> DROP ACCESS POLICY ON customer_info FOR ROWS;

2 - DROP AGGREGATE FUNCTION

Drops a user-defined aggregate function (UDAnF) from the Vertica catalog.

Drops a user-defined aggregate function (UDAnF) from the Vertica catalog.

Syntax

DROP AGGREGATE FUNCTION [ IF EXISTS ] [[database.]schema.]function( [ arglist ] )

Parameters

IF EXISTS
Specifies not to report an error if the function to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
[database.]schema

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

function
Specifies a name of the SQL function to drop. If the function name is schema-qualified, the function is dropped from the specified schema (as noted above).
arglist
A comma delimited list of argument names and data types that are passed to the function, formatted as follows: 
{ [argname] argtype }[,...]

  • argname optionally specifies the argument name, typically a column name.

  • argtype specifies the argument's data type, where argtype matches a Vertica data type.

Privileges

Non-superuser: Owner

Requirements

  • To drop a function, you must specify the argument types because several functions might share the same name with different parameters.

  • Vertica does not check for dependencies, so if you drop a SQL function where other objects reference it (such as views or other SQL functions), Vertica returns an error when those objects are used and not when the function is dropped.

Examples

The following command drops the ag_avg function:

=> DROP AGGREGATE FUNCTION ag_avg(numeric);
DROP AGGREGATE FUNCTION

See also

Aggregate functions (UDAFs)

3 - DROP ANALYTIC FUNCTION

Drops a user-defined analytic function from the Vertica catalog.

Drops a user-defined analytic function from the Vertica catalog.

Syntax

DROP ANALYTIC FUNCTION [ IF EXISTS ] [[database.]schema.]function( [ arglist ] )

Parameters

IF EXISTS
Specifies not to report an error if the function to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
[database.]schema

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

function
Specifies a name of the SQL function to drop. If the function name is schema-qualified, the function is dropped from the specified schema (as noted above).
arglist
A comma delimited list of argument names and data types that are passed to the function, formatted as follows: 
{ [argname] argtype }[,...]

  • argname optionally specifies the argument name, typically a column name.

  • argtype specifies the argument's data type, where argtype matches a Vertica data type.

Privileges

Non-superuser: Owner

Requirements

  • To drop a function, you must specify the argument types because several functions might share the same name with different parameters.

  • Vertica does not check for dependencies, so if you drop a SQL function where other objects reference it (such as views or other SQL functions), Vertica returns an error when those objects are used and not when the function is dropped.

Examples

The following command drops the analytic_avg function:

=> DROP ANALYTIC FUNCTION analytic_avg(numeric);
DROP ANALYTIC FUNCTION

See also

Analytic functions (UDAnFs)

4 - DROP AUTHENTICATION

Drops an authentication method.

Drops an authentication method.

Syntax

DROP AUTHENTICATION [ IF EXISTS ] auth-method-name [ CASCADE ]

Parameters

IF EXISTS
Specifies not to report an error if the authentication method to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
auth-method-name
Name of the authentication method to drop.
CASCADE
Required if the authentication method to drop is granted to users. In this case, omission of this option causes the drop operation to fail.

Privileges

Superuser

Examples

Delete authentication method md5_auth:

=> DROP AUTHENTICATION md5_auth;

Use CASCADE to drop authentication method that was granted to a user:

=> CREATE AUTHENTICATION localpwd METHOD 'password' LOCAL;
=> GRANT AUTHENTICATION localpwd TO jsmith;
=> DROP AUTHENTICATION localpwd CASCADE;

See also

5 - DROP CA BUNDLE

Drops a certificate authority (CA) bundle.

Drops a certificate authority (CA) bundle.

Syntax

DROP CA BUNDLE [ IF EXISTS ] name [,...] [ CASCADE ]

Parameters

IF EXISTS
Vertica does not report an error if the CA bundle to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
name
The name of the CA bundle.
CASCADE
Drops dependent objects before dropping the CA bundle.

Privileges

Ownership of the CA bundle

Examples

See Managing CA bundles.

See also

6 - DROP CERTIFICATE

Drops a TLS certificate from the database.

Drops a TLS certificate from the database.

To view existing certificates, query CERTIFICATES.

Syntax

DROP CERTIFICATE [ IF EXISTS ] certificate-name [,...] [ CASCADE ]

Parameters

IF EXISTS
Vertica does not report an error if the certificate to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
certificate-name
The name of the certificate to drop.
CASCADE
Drops dependent objects before dropping the certificate.

Predefined TLS Configurations and TLS Configurations that manage a connection type cannot be dropped, nor can the keys and certificates referenced by such TLS Configurations. For details, see TLS configurations.

Privileges

Non-superuser, one of the following:

  • Ownership of the certificate

  • DROP privileges on the private key (when used with DROP KEY...CASCADE)

Examples

Drop server_cert, if it exists:

=> DROP CERTIFICATE server_cert;
DROP CERTIFICATE;

Drop a CA certificate and its dependencies (typically the certificates that it has signed):

=> DROP CERTIFICATE ca_cert CASCADE;
DROP CERTIFICATE;

See also

7 - DROP DATA LOADER

DROP DATA LOADER drops a data loader and its associated monitoring table.

DROP DATA LOADER drops a data loader created with CREATE DATA LOADER. It also drops the associated monitoring table.

If the data loader was created with a trigger, you must disable it before dropping it. Call ALTER DATA LOADER with the DISABLE option.

Syntax

DROP DATA LOADER [ IF EXISTS ] [schema.]name

Arguments

IF EXISTS
Do not report an error if the data loader does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
schema
Schema containing the data loader. The default schema is public.
name
Name of the data loader.

Privileges

Non-superuser: owner or DROP privilege on the data loader.

See also

8 - DROP DIRECTED QUERY

Removes a directed query from the database.

Removes a directed query from the database. If the directed query is active, Vertica deactivates it before removal.

Syntax

DROP DIRECTED QUERY { query-name | where-clause }

Arguments

query-name
Name of the directed query to remove from the database, as stored in the DIRECTED_QUERIES column query_name. You can also use GET DIRECTED QUERY to obtain names of all directed queries that map to an input query.
where-clause
Resolves to one or more directed queries that are filtered from system table DIRECTED_QUERIES. For example, the following statement specifies to drop all directed queries with the same save_plans_version identifier: 
=> DROP DIRECTED QUERY WHERE save_plans_version = 21;

Privileges

Superuser

Examples

See Managing directed queries.

9 - DROP FAULT GROUP

Removes the specified fault group and its child fault groups, placing all nodes under the parent of the dropped fault group.

Removes the specified fault group and its child fault groups, placing all nodes under the parent of the dropped fault group.

To drop all fault groups, use ALTER DATABASE..DROP ALL FAULT GROUP.

To add an orphaned node back to a fault group, you must manually reassign it to a new or existing fault group with CREATE FAULT GROUP and ALTER FAULT GROUP...ADD NODE.

Syntax

DROP FAULT GROUP [ IF EXISTS ] fault-group

Parameters

IF EXISTS
Specifies not to report an error if fault-group does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
fault-group
Specifies the name of the fault group to drop.

Privileges

Superuser

Examples

=> DROP FAULT GROUP group2;
DROP FAULT GROUP

See also

10 - DROP FILTER

Drops a User Defined Load Filter function from the Vertica catalog.

Drops a User Defined Load Filter function from the Vertica catalog.

Syntax

DROP FILTER [[database.]schema.]filter()

Parameters

[database.]schema

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

filter()
Specifies the filter function to drop. You must append empty parentheses to the function name.

Privileges

Non-superuser:

Examples

The following command drops the Iconverter filter function::

=> drop filter Iconverter();
DROP FILTER

See also

11 - DROP FUNCTION

Drops an SQL function or user-defined functions (UDFs) from the Vertica catalog.

Drops an SQL function or user-defined functions (UDFs) from the Vertica catalog.

Syntax

DROP FUNCTION [ IF EXISTS ] [[database.]schema.]function[,...] ( [ arg-list ] )

Parameters

IF EXISTS
Specifies not to report an error if the function to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
[database.]schema

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

function
The SQL or user-defined function (UDF) to drop, where UDFs can be one of the following types:
arg-list

A comma-delimited list of arguments as defined for this function when it was created, specified as follows:

[arg-name] arg-type[,...]

where arg-name optionally qualifies arg-type:

  • arg-name is typically a column name.

  • arg-type is the name of an SQL data type supported by Vertica.

Privileges

Non-superuser, one of the following:

Requirements

  • To drop a function, you must specify the argument types because several functions might share the same name with different parameters.

  • Vertica does not check for dependencies when you drop a SQL function, so if other objects reference it (such as views or other SQL functions), Vertica returns an error only when those objects are used.

Examples

The following command drops the zerowhennull function in the macros schema:

=>  DROP FUNCTION macros.zerowhennull(x INT);
DROP FUNCTION

See also

12 - DROP KEY

Drops a cryptographic key and its certificate, if any, from the database.

Drops a cryptographic key and its certificate, if any, from the database.

To view existing cryptographic keys, query CRYPTOGRAPHIC_KEYS.

Syntax

DROP KEY [ IF EXISTS ] key-name [,...] [ CASCADE ]

Parameters

IF EXISTS
Vertica does not report an error if the key to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
key-name
The name of the cryptographic key to drop.
CASCADE
Drops dependent objects before dropping the key.

Predefined TLS Configurations and TLS Configurations that manage a connection type cannot be dropped, nor can the keys and certificates referenced by such TLS Configurations. For details, see TLS configurations.

Privileges

Non-superuser, one of the following:

  • Ownership of the key

  • DROP privileges

Examples

Drop k_ca, if it exists:

=> DROP KEY k_ca IF EXISTS;
DROP KEY;

Drop k_client and its dependencies (the certificate it's associated with):

=> DROP KEY k_client CASCADE;
DROP KEY;

See also

13 - DROP LIBRARY

Removes a UDx library from the database.

Removes a UDx library from the database. The library file is deleted from managed directories on the Vertica nodes. The user-defined functions (UDFs) in the library are no longer available. See Developing user-defined extensions (UDxs) for details.

Syntax

DROP LIBRARY [ IF EXISTS ] [[database.]schema.]library [  CASCADE]

Arguments

IF EXISTS
Execute this command only if the library exists. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
[database.]schema

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

library
The name of the library to drop, the same name used in CREATE LIBRARY to load the library.
CASCADE
Also drop any functions that were defined using the library. DROP LIBRARY fails if CASCADE is omitted and one or more UDxs use the target library.

Privileges

One of:

Examples

A superuser can drop any library:

=> DROP LIBRARY ml.MyLib CASCADE;

Users with the UDXDEVELOPER role can drop libraries that they created:

=> GRANT UDXDEVELOPER TO alice, bob;
GRANT ROLE

=> \c - alice;
You are now connected as user "alice".

-- Must enable the role before using:
=> SET ROLE UDXDEVELOPER;
SET

-- Create and use ml.mylib...

-- Drop library and dependencies:
DROP LIBRARY ml.mylib CASCADE;
DROP LIBRARY

A user can be granted explicit permission to drop a library:

=> \c - alice
You are now connected as user "alice".

=> GRANT DROP ON LIBRARY ml.mylib to bob;
GRANT PRIVILEGE

=> \c - bob
You are now connected as user "bob".

=> SET ROLE UDXDEVELOPER;
SET

=> DROP LIBRARY ml.mylib cascade;
DROP LIBRARY

14 - DROP LOAD BALANCE GROUP

Deletes a load balancing group.

Deletes a load balancing group.

Syntax

DROP LOAD BALANCE GROUP [ IF EXISTS ] group_name [ CASCADE ]

Parameters

IF EXISTS
Specifies not to report an error if the load balance group to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
group_name
The name of the group to drop.
[CASCADE]
Also drops all load balancing routing rules that target this group. If you do not supply this keyword and one or more routing rules target group_name, this statement fails with an error message.

Privileges

Superuser

Examples

The following statement demonstrates the error you get if the load balancing group has a dependent routing rule, and the use of the CASCADE keyword:

=> DROP LOAD BALANCE GROUP group_all;
NOTICE 4927:  The RoutingRule catch_all depends on LoadBalanceGroup group_all
ROLLBACK 3128:  DROP failed due to dependencies
DETAIL:  Cannot drop LoadBalanceGroup group_all because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too

=> DROP LOAD BALANCE GROUP group_all CASCADE;
DROP LOAD BALANCE GROUP

See also

15 - DROP MODEL

Removes one or more models from the Vertica database.

Removes one or more models from the Vertica database.

Syntax

DROP MODEL [ IF EXISTS ] [[database.]schema.]model[,...]

Parameters

IF EXISTS
Specifies not to report an error if the models to drop do not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
[database.]schema

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

model
The model to drop.

Privileges

One of the following:

  • Superuser

  • Non-superuser: model owner

Examples

See Dropping models.

16 - DROP NAMESPACE

Eon Mode only

Drops a namespace from an Eon Mode database. The statement errors if there are any schemata or tables in the namespace.

Syntax

DROP NAMESPACE [ IF EXISTS ] name

Arguments

IF EXISTS
Do not report an error if the namespace does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
name
Name of the namespace to drop. You cannot drop the default_namespace.

Privileges

Superuser

Examples

Drop the analytics namespace:

=> DROP NAMESAPCE `analytics`;
DROP NAMESPACE

See also

17 - DROP NETWORK ADDRESS

Deletes a network address from the catalog.

Deletes a network address from the catalog. A network address is a name for a IP address and port on a node for use in connection load balancing policies.

Syntax

DROP NETWORK ADDRESS [ IF EXISTS ] address-name [ CASCADE ]

Parameters

IF EXISTS
Specifies not to report an error if the network address to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
address-name
Name of the network address to drop.
CASCADE
Removes the network address from any load balancing groups that target it. If you do not supply this keyword and one or more load balance groups include this address, this statement fails with an error message.

Privileges

Superuser

Examples

The following statement demonstrates the error you get if the network address has a dependent load balance group, and the use of the CASCADE keyword:

=> DROP NETWORK ADDRESS node01;
NOTICE 4927:  The LoadBalanceGroup group_1 depends on NetworkInterface node01
NOTICE 4927:  The LoadBalanceGroup group_random depends on NetworkInterface node01
ROLLBACK 3128:  DROP failed due to dependencies
DETAIL:  Cannot drop NetworkInterface node01 because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too
=> DROP NETWORK ADDRESS node01 CASCADE;
DROP NETWORK ADDRESS

18 - DROP NETWORK INTERFACE

Removes a network interface from Vertica.

Removes a network interface from Vertica. You can use the CASCADE option to also remove the network interface from any node definition. (See Identify the database or nodes used for import/export for more information.)

Syntax

DROP NETWORK INTERFACE [ IF EXISTS ] network-interface-name [ CASCADE ]

Parameters

The parameters are defined as follows:

IF EXISTS
Specifies not to report an error if the network interface to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
network-interface-name
The network interface to remove.
CASCADE
Removes the network interface from all node definitions.

Privileges

Superuser

Examples

=> DROP NETWORK INTERFACE myNetwork;

19 - DROP NOTIFIER

Drops a push-based notifier created by CREATE NOTIFIER.

Drops a push-based notifier created by CREATE NOTIFIER.

Syntax

DROP NOTIFIER [ IF EXISTS ] notifier-name [ CASCADE ]

Parameters

IF EXISTS
Specifies not to report an error if notifier to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
notifier-name
The notifier's unique identifier.
CASCADE
Removes the notifier from any data collector (DC) table policies before dropping the notifier. If the notifier is set for a DC table and CASCADE is not specified, the DROP command fails.

To manually remove the notifier from DC table policies, use the SET_DATA_COLLECTOR_NOTIFY_POLICY function.

Examples

Drop the requests_issued notifier, specifying CASCADE to remove it from any DC table policies:

DROP NOTIFIER requests_issued CASCADE;

20 - DROP PARSER

Drops a User Defined Load Parser function from the Vertica catalog.

Drops a User Defined Load Parser function from the Vertica catalog.

Syntax

DROP PARSER[[database.]schema.]parser()

Parameters

[database.]schema

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

parser()
The name of the parser function to drop. You must append empty parentheses to the function name.

Privileges

Non-superuser:

Examples

=> DROP PARSER BasicIntegerParser();
DROP PARSER

See also

21 - DROP PROCEDURE (external)

Removes an external procedure from Vertica.

Enterprise Mode only

Removes an external procedure from Vertica. Only the reference to the procedure inside Vertica is removed. The external file remains in the database/procedures directory of each database node.

Syntax

DROP PROCEDURE [ IF EXISTS ] [[database.]schema.]procedure( [ parameter-list ] )

Parameters

IF EXISTS
Specifies not to report an error if the procedure to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
[database.]schema

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

procedure
Specifies the procedure to drop.
parameter-list
A comma-delimited list of formal parameters defined for this procedure, specified as follows:

[parameter-name] parameter-type[,...]

where parameter-name optionally qualifies parameter-type.

Privileges

Non-superuser:

Examples

=> DROP PROCEDURE helloplanet(arg1 varchar);

See also

CREATE PROCEDURE (external)

22 - DROP PROCEDURE (stored)

Drops a stored procedure.

Drops a stored procedure.

Syntax

DROP PROCEDURE [ IF EXISTS ] [[database.]schema.]procedure( [ parameter-type-list] ) [ CASCADE ];

Parameters

IF EXISTS
Specifies not to report an error if the procedure to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
[database.]schema

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

procedure
The name of the stored procedure, where procedure conforms to conventions described in Identifiers.
parameter-type-list
A comma-delimited list of the IN and INOUT parameters' types.
CASCADE
Drops the trigger that references the stored procedure, if any.

Privileges

Non-superuser:

  • Owner or DROP privilege

  • USAGE privilege on schema

Examples

Given the following procedure:

=> CREATE PROCEDURE raiseXY(IN x INT, y VARCHAR) LANGUAGE PLvSQL AS $$
BEGIN
    RAISE NOTICE 'x = %', x;
    RAISE NOTICE 'y = %', y;
    -- some processing statements
END;
$$;

CALL raiseXY(3, 'some string');
NOTICE 2005:  x = 3
NOTICE 2005:  y = some string

You can drop it with:

=> DROP PROCEDURE raiseXY(INT, VARCHAR);
DROP PROCEDURE

For details on RAISE NOTICE, see Errors and diagnostics.

See also

23 - DROP PROFILE

Removes a user-defined profile (created by CREATE PROFILE) from the database.

Removes a user-defined profile (created by CREATE PROFILE) from the database. You cannot drop the DEFAULT profile.

Syntax

DROP PROFILE [ IF EXISTS ] profile-name[,...] [ CASCADE ]

Parameters

IF EXISTS
Specifies not to report an error if the profile to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
profile-name
The profile to drop.
CASCADE
Moves all users assigned to the dropped profiles to the DEFAULT profile. If you omit this option and a targeted profile has users assigned to it, Vertica returns an error.

Privileges

Superuser

Examples

=> DROP PROFILE sample_profile;

24 - DROP PROJECTION

Marks a to drop from the catalog so it is unavailable to user queries.

Marks a projection to drop from the catalog so it is unavailable to user queries.

Syntax

DROP PROJECTION [ IF EXISTS ] { [[database.]schema.]projection[,...] } [ RESTRICT | CASCADE ]

Parameters

IF EXISTS
Specifies not to report an error if the projection to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
[database.]schema

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

projection
Specifies a projection to drop:

  • If the projection is unsegmented, all projection replicas in the database cluster are dropped.

  • If the projection is segmented, drop all buddy projections by specifying the projection base name. You can also specify the name of a specific buddy projection as long as dropping it so does not violate system K-safety.

See Projection naming for projection name conventions.

RESTRICT | CASCADE
Specifies whether to drop the projection when it contains objects:

  • RESTRICT (default): Drop the projection only if it contains no objects.

  • CASCADE: Drop the projection even if it contains objects.

Privileges

Non-superuser: owner of the anchor table

Restrictions

The following restrictions apply to dropping a projection:

  • The projection cannot be the anchor table's superprojection.

  • You cannot drop a buddy projection if doing so violates system K-safety.

  • Another projection must be available to enforce the same primary or unique key constraint.

See also

25 - DROP RESOURCE POOL

Drops a user-created resource pool.

Drops a user-created resource pool. All memory allocated to the pool is returned to the GENERAL pool.

Syntax

DROP RESOURCE POOL resource-pool [ FOR { SUBCLUSTER subcluster | CURRENT SUBCLUSTER } ]

Parameters

resource-pool
Name of the resource pool to drop.
FOR {SUBCLUSTER subcluster | CURRENT SUBCLUSTER}
Eon Mode only, drops resource-pool from the specified subcluster, one of the following:
  • SUBCLUSTER subcluster: Drops resource-pool from the named subcluster . You cannot be connected to this subcluster, otherwise Vertica returns an error.
  • CURRENT SUBCLUSTER: Drops resource-pool from the subcluster you are connected to.

If you omit this parameter, the resource pool is dropped from all subclusters. If a resource pool was created for an individual subcluster, you must explicitly drop it from that subcluster with this parameter; otherwise, Vertica returns an error.

Privileges

Superuser

Resource pool transfers

Requests that are queued against the dropped pool are transferred to the GENERAL pool according to the priority of the pool compared to the GENERAL pool. If the pool’s priority is higher than the GENERAL pool, the requests are placed at the head of the queue; otherwise, transferred requests are placed at the end of the queue.

Users who are assigned to the dropped pool are reassigned to the default user resource pool as set by DefaultResourcePoolForUser. The DROP request returns with a notice like this:

NOTICE:  Switched the following users to the <name> pool: <username>

If any user lacks permission to use the default user resource pool, Vertica rolls back the drop operation.

Existing sessions are transferred to the GENERAL pool regardless of whether the session user has privileges to use that pool. This can result in additional user privileges if access to the dropped pool is more restrictive than the GENERAL pool. In this case, you can prevent giving users additional privileges as follows:

  1. Revoke permissions on the target resource pool from all users.

  2. Close any sessions that had permissions on the resource pool.

  3. Drop the resource pool.

Restrictions

  • If you try to drop a resource pool that is the secondary pool for another resource pool, Vertica returns an error. The error lists the resource pools that depend on the secondary pool you tried to drop. To drop a secondary resource pool, first set the CASCADE TO parameter to DEFAULT on the primary resource pool, and then drop the secondary pool.

    For example, you can drop resource pool rp2, which is a secondary pool for rp1, as follows:

    => ALTER RESOURCE POOL rp1 CASCADE TO DEFAULT;
=> DROP RESOURCE POOL rp2;

  • You cannot drop a resource pool that is configured as the default user resource pool by the DefaultResourcePoolForUsers parameter.

Examples

Drop a user-defined resource pool:

=> DROP RESOURCE POOL ceo_pool;

Get the name of the current subcluster for an Eon Mode database, then drop its resource pool:

=> SELECT CURRENT_SUBCLUSTER_NAME();
 CURRENT_SUBCLUSTER_NAME
-------------------------
 analytics_1
(1 row)

=> DROP RESOURCE POOL dashboard FOR CURRENT SUBCLUSTER;
DROP RESOURCE POOL

See also

26 - DROP ROLE

Removes a role from the database.

Removes a role from the database.

Syntax

DROP ROLE [ IF EXISTS ] role-name[,...] [ CASCADE ]

Parameters

IF EXISTS
Specifies not to report an error if the roles to drop do not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
role-name
The name of the role to drop
CASCADE
Revoke the role from users and other roles before dropping the role

Privileges

Superuser

Examples

=> DROP ROLE appadmin;
NOTICE:  User bob depends on Role appadmin
ROLLBACK:  DROP ROLE failed due to dependencies
DETAIL:  Cannot drop Role appadmin because other objects depend on it
HINT:  Use DROP ROLE ... CASCADE to remove granted roles from the dependent users/roles
=> DROP ROLE appadmin CASCADE;
DROP ROLE

See also

27 - DROP ROUTING RULE

Deletes a routing rule from the catalog.

Deletes a routing rule from the catalog.

Syntax

DROP ROUTING RULE [ IF EXISTS ] { rule_name | FOR WORKLOAD workload_name }

Parameters

IF EXISTS
Specifies not to report an error if the routing rule to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
rule_name
Name of the rule to drop.
workload_name
The name of a workload.

Privileges

Superuser

Examples

To drop a routing rule:

=> DROP ROUTING RULE internal_clients;
DROP ROUTING RULE

To drop a rule for a workload:

=> DROP ROUTING RULE FOR WORKLOAD analytics;

See also

28 - DROP SCHEDULE

Drops schedules.

Drops schedules.

Syntax

DROP SCHEDULE [[database.]schema.]schedule[,...] [ CASCADE ]

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 drop.
CASCADE
Drops triggers that use this schedule, if any.

Privileges

Superuser

Examples

=> DROP SCHEDULE monthly_schedule;

29 - DROP SCHEMA

Permanently removes a schema from the database.

Permanently removes a schema from the database. Be sure that you want to remove the schema before you drop it, because DROP SCHEMA is an irreversible process. Use the CASCADE parameter to drop a schema containing one or more objects.

Syntax

DROP SCHEMA [ IF EXISTS ] [{namespace. | database. }]schema[,...] [ CASCADE | RESTRICT ]

Parameters

IF EXISTS
Specifies not to report an error if the schemas to drop do not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
{ database | namespace }
Name of the database or namespace that contains schema:

  • Database name: If specified, it must be the current database.

  • Namespace name (Eon Mode only): You must specify the namespace of objects in non-default namespaces. If no namespace is provided, Vertica assumes the object is in the default namespace.

schema
Name of the schema, by default public.

If you specify a schema name that contains a period, the part before the period cannot be the same as the name of an existing namespace.

CASCADE
Specifies to drop the schema and all objects in it, regardless of who owns those objects.
RESTRICT
Drops the schema only if it is empty (default).

Privileges

Non-superuser: schema owner

Restrictions

  • You cannot drop the PUBLIC schema.

  • If a user is accessing an object within a schema that is in the process of being dropped, the schema is not deleted until the transaction completes.

  • Canceling a DROP SCHEMA statement can cause unpredictable results.

Examples

The following example drops schema S1 only if it doesn't contain any objects:

=> DROP SCHEMA S1;

The following example drops schema S1 whether or not it contains objects:

=> DROP SCHEMA S1 CASCADE;

30 - DROP SEQUENCE

Removes the specified named sequence number generator.

Removes the specified named sequence number generator.

Syntax

DROP SEQUENCE [ IF EXISTS ] [[database.]schema.]sequence[,...]

Parameters

IF EXISTS
Specifies not to report an error if the sequences to drop do not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
[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 drop.

Privileges

Non-superusers: sequence or schema owner

Restrictions

  • For sequences specified in a table's default expression, the default expression fails the next time you try to load data. Vertica does not check for these instances.

  • DROP SEQUENCE does not support the CASCADE keyword. Sequences used in a default expression of a column cannot be dropped until all references to the sequence are removed from the default expression.

Examples

The following command drops the sequence named sequential.

=> DROP SEQUENCE sequential;

See also

31 - DROP SOURCE

Drops a User Defined Load Source function from the Vertica catalog.

Drops a User Defined Load Source function from the Vertica catalog.

Syntax

DROP SOURCE [[database.]schema.]source()

Parameters

[database.]schema

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

source()
Specifies the source function to drop. You must append empty parentheses to the function name.

Privileges

Non-superuser:

Examples

The following command drops the curl source function:

=> DROP SOURCE curl();
DROP SOURCE

See also

32 - DROP SUBNET

Removes a subnet from Vertica.

Removes a subnet from Vertica.

Syntax

DROP SUBNET [ IF EXISTS ] subnet-name[,...] [ CASCADE ]

Parameters

The parameters are defined as follows:

IF EXISTS
Specifies not to report an error if the subnets to drop do not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
subnet-name
A subnet to remove.
CASCADE
Removes the specified subnets from all database definitions.

Privileges

Superuser

Examples

=> DROP SUBNET mySubnet;

See also

Identify the database or nodes used for import/export

33 - DROP TABLE

DROP TABLE;delete table;.

Removes one or more tables and their projections. When you run DROP TABLE, the change is auto-committed.

Syntax

DROP TABLE [ IF EXISTS ] [[{namespace. | database. }]schema.]table[,...] [ CASCADE ]

Parameters

IF EXISTS
Specifies not to report an error if one or more of the tables to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
{ database | namespace }
Name of the database or namespace that contains table:

  • Database name: If specified, it must be the current database.

  • Namespace name (Eon Mode only): You must specify the namespace of objects in non-default namespaces. If no namespace is provided, Vertica assumes the object is in the default namespace.

schema
Name of the schema, by default public. If you specify the namespace or database name, you must provide the schema name, even if the schema is public.

If you specify a schema name that contains a period, the part before the period cannot be the same as the name of an existing namespace.

table
The table to drop.
CASCADE
Specifies to drop all projections of the target tables. CASCADE is optional if the target tables have only auto-projections. If you omit this option and any of the tables has non-superprojections, Vertica returns an error and rolls back the entire drop operation.

This option is not valid for external tables.

Privileges

Non-superuser:

Requirements

  • Do not cancel an executing DROP TABLE. Doing so can leave the database in an inconsistent state.

  • Check that the target table is not in use, either directly or indirectly—for example, in a view.

  • If you drop and restore a table that is referenced by a view, the new table must have the same name and column definitions.

Examples

See Dropping tables

See also

34 - DROP TEXT INDEX

Drops a text index used to perform text searches.

Drops a text index used to perform text searches.

Syntax

DROP TEXT INDEX [ IF EXISTS ] [[database.]schema.]idx-table

Parameters

IF EXISTS
Specifies not to report an error if the text index to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
[database.]schema

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

idx-table
Specifies the text index name. When using more than one schema, specify the schema that contains the index in the DROP TEXT INDEX statement.

Privileges

  • dbadmin

  • Table owner

  • DROP privileges on the source table

Examples

=> DROP TEXT INDEX t_text_index;
DROP INDEX

See also

35 - DROP TLS CONFIGURATION

Drops an existing TLS Configuration.

Drops an existing TLS Configuration.

You cannot drop a TLS Configuration if it set as a configuration parameter. For details, see TLS configurations.

Syntax

DROP TLS CONFIGURATION tls_config_name

Parameters

tls_config_name
The name of the TLS Configuration object to drop.

Privileges

Non-superuser, one of the following:

36 - DROP TRANSFORM FUNCTION

Drops a user-defined transform function (UDTF) from the Vertica catalog.

Drops a user-defined transform function (UDTF) from the Vertica catalog.

Syntax

DROP TRANSFORM FUNCTION [ IF EXISTS ] [[database.]schema.]function( [ arg-list ] )

Parameters

IF EXISTS
Specifies not to report an error if the function to drop does not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
[database.]schema

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

function
Specifies the transform function to drop.
arg-list

A comma-delimited list of arguments as defined for this function when it was created, specified as follows:

[arg-name] arg-type[,...]

where arg-name optionally qualifies arg-type:

  • arg-name is typically a column name.

  • arg-type is the name of an SQL data type supported by Vertica.

Privileges

One of the following:

Examples

The following command drops the tokenize UDTF in the macros schema:

=> DROP TRANSFORM FUNCTION macros.tokenize(varchar);
DROP TRANSFORM FUNCTION

The following command drops the Pagerank polymorphic function in the online schema:

=> DROP TRANSFORM FUNCTION online.Pagerank();
DROP TRANSFORM FUNCTION

See also

CREATE TRANSFORM FUNCTION

37 - DROP TRIGGER

Drops triggers.

Drops triggers. Dropping a trigger disables its associated schedule, if any.

Syntax

DROP TRIGGER [[database.]schema.]trigger[,...]

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 drop.

Privileges

Superuser

Examples

To drop a trigger:

=> DROP TRIGGER revoke_trigger;

38 - DROP USER

Removes a name from the list of authorized database users.

Removes a name from the list of authorized database users.

Syntax

DROP USER [ IF EXISTS ] user-name[,...] [ CASCADE ]

Parameters

IF EXISTS
Do not report an error if the users to drop do not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
user-name
Name of a user to drop.
CASCADE
Drop all user-defined objects created by user-name, including schemas, tables and all views that reference the table, and projections of that table.

Privileges

Superuser

Examples

DROP USER succeeds if no user-defined objects exist:

=> CREATE USER user2;
CREATE USER
=> DROP USER IF EXISTS user2;
DROP USER

DROP USER fails if objects that the user created still exist:

=> DROP USER IF EXISTS user1;
NOTICE:  Table T_tbd1 depends on User user1
ROLLBACK:  DROP failed due to dependencies
DETAIL:  Cannot drop User user1 because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too

DROP USER CASCADE succeeds regardless of any existing user-defined objects. The statement forcibly drops all user-defined objects, such as schemas, tables and their associated projections:

=> DROP USER IF EXISTS user1 CASCADE;
DROP USER

See also

39 - DROP VIEW

Removes the specified view.

Removes the specified view. Vertica does not check for dependencies on the dropped view. After dropping a view, other views that reference it fail.

If you drop a view and replace it with another view or table with the same name and column names, other views that reference that name use the new view. If you change the column data type in the new view, the server coerces the old data type to the new one if possible; otherwise, it returns an error.

Syntax

DROP VIEW [ IF EXISTS ] [[database.]schema.]view[,...]

Parameters

IF EXISTS
Specifies not to report an error if the views to drop do not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.
[database.]schema

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

view
Name of a view to drop.

Privileges

One of the following

  • View owner and USAGE privileges

  • Schema owner

Examples

=> DROP VIEW myview;