DROP TABLE
Drops a 
table and optionally its associated projections.
A projection is a special case of a materialized view that provides physical storage for data. A projection can contain some or all of the columns of one or more tables. A projection that contains all of the columns of a table is called a superprojection. A projection that joins one or more tables is called a pre-join projection. Most projections are used for ad-hoc query processing and K-safety but it is possible to have query-specific projections.
A superprojection is a projection that contains every column of a table in the Logical Schema. A table can have multiple superprojections with different sort orders.
A materialized view is similar to a standard SQL view with one major exception: the data is actually stored on disk rather than computed each time the view is used in a query. A materialized view, then, must be refreshed whenever the data in the underlying tables is changed. A projection is a special case of a materialized view.
In the relational model, a table (relation) is a set of data elements (values) organized into horizontal rows (tuples) and vertical columns. A table has a specified number of columns but can have any number of rows.
In Vertica, a table is a metadata-only entity referred to in queries. The physical representation of data is a projection.
Syntax
DROP TABLE table [ CASCADE ]
Semantics
table
|
specifies the name of a schema table
|
CASCADE
|
drops all projections that include the table.
Warning: dropping a table and its associated projections is very likely to destroy the K-Safety of your physical schema design. Use this command only when absolutely necessary.
K represents the maximum number of nodes in a database that can fail and recover with no loss of data. In Vertica V2.1, the value of K can be zero (0) or one (1). The value of K can be one (1) only when the Physical Schema design meets certain requirements. The designs generated by the Database Designer are K-Safe.
A physical schema consists of a set of projections used to store data on disk. The projections in the physical schema are based on the objects in the Logical Schema.
|
if you try to drop an table that has associated projections, you will get a message listing the projections. For example:
=> DROP TABLE d1;
NOTICE: Constraint - depends on Table d1
NOTICE: Projection d1p1 depends on Table d1
NOTICE: Projection d1p2 depends on Table d1
NOTICE: Projection d1p3 depends on Table d1
NOTICE: Projection f1d1p1 depends on Table d1
NOTICE: Projection f1d1p2 depends on Table d1
NOTICE: Projection f1d1p3 depends on Table d1
ERROR: DROP failed due to dependencies: Cannot drop Table d1 because other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.
Notes
- Cancelling a DROP TABLE statement can cause unpredictable results.
- Vertica Systems, Inc. recommends that you gain exclusive access to the database before using DROP TABLE. In other words, make sure that all other users have disconnected.
SQL Language References
PostgreSQL 8.0.12 Documentation
BNF Grammar for SQL-99
