Drop Partition

     Using drop partition command we can drop the table partitions.

Syntax: 
SELECT DROP_PARTITION ('<Table Name>',<Partition Name>,true)

Example:
SELECT DROP_PARTITION ('Sample',1,true)

 
See also: Create Table Partitions

Sample table with table partitions on ID Column:

Applying Drop partition command:

Data after applying drop partition command:

Creating Table Partitions

   This post explains how to do the table partition with examples.

    Vertica supports data partitioning at the table level, which divides one large table into smaller pieces. Partitions are a property of the table.

Example:

Table Creation script:

create table sample (id int,name varchar(10))

insert into sample values (1,'a');

insert into sample values (1,'b');

insert into sample values (1,'c');

insert into sample values (2,'a');

insert into sample values (2,'b');

insert into sample values (2,'c');

• I am going to create partitions based on ID Column. So first we need to set ID column as NOT NULL.

ALTER TABLE sample  alter column id set not null;

•  Then run the table partition script

ALTER TABLE sample  PARTITION BY id REORGANIZE;

• Use below commands to check the table partition status. If partition reorganize status is 100% then partitioning is completed. If any error is happened then error table will populate.
  select * from V_MONITOR.PARTITION_STATUS where table_name='sample'

  

select * from V_MONITOR.PARTITION_REORGANIZE_ERRORS where table_name='sample' 

See Also: Drop Partition

Set Default Search Path

      Using alter command we can set the user default search path.

Syntax:
ALTER USER <User Name> SEARCH_PATH <Schema Name>, "$user", public;

Example:
ALTER USER sample_user SEARCH_PATH test_schema, "$user", public;


See also:

•  User creation
• Grant usage on schema public
• Create Schema
• Grant usage privileges on a schema to the user
• Grant all privileges on a schema to the user

Grant all Privileges on a Schema to the user

   Using below syntax we can grant all privileges on a schema to required user.

Syntax:
GRANT ALL ON SCHEMA <Schema Name> TO <User Name>;

Example:
GRANT ALL ON SCHEMA test_schema TO sample_user;


See also:

• User creation
• Grant usage on schema public
• Create Schema
• Grant usage privileges on a schema to the user
• Set default search path

Grant Usage Privileges on a Schema to the user

    Using below syntax we can grant usage privileges on a schema to required user.

Syntax:
GRANT USAGE ON SCHEMA <Schema Name> TO <User Name>;

Example:
GRANT USAGE ON SCHEMA test_schema TO sample_user;


See also:

• User creation
• Grant usage on schema public
• Create Schema
• Grant all privileges on a schema to the user
• Set default search path

Grant Usage On Public Schema

      Using below syntax we can grant usage privileges on Public schema to the user.

Syntax:
GRANT USAGE ON SCHEMA PUBLIC to <User Name>

Example:
GRANT USAGE ON SCHEMA PUBLIC to Test_User


See also:

•  User creation
• Grant usage on schema public
• Create Schema
• Grant all privileges on a schema to the user
• Set default search path

Create Schema

    Connect to vertica database super user. Using create statement we can create a new schema.

Syntax:
CREATE SCHEMA <Schema Name>;

Example:
CREATE SCHEMA test_schema;


See also:

•  User creation
• Grant usage on schema public
• Grant usage privileges on a schema to the user
• Grant all privileges on a schema to the user
• Set default search path

How to create a copy of existing table

      Using below command we can create a new table using existing table.
Syntax:
CREATE TABLE <New Table Name> AS SELECT * FROM <Existing table name>

Example:
CREATE TABLE SAMPLE_TBL_BKP AS SELECT * FROM SAMPLE_TBL

If you need only table structure then use below command:
CREATE TABLE SAMPLE_TBL_BKP AS SELECT * FROM SAMPLE_TBL WHERE 1 = 2



Note: If we use this command to create a new table, that new table will have only super projection (It will come from source). Source table user define projections will not come.