isnumeric function

isnumeric function is not a built in function in Vertica, but we can create it.

CREATE FUNCTION ISNUMERIC(X VARCHAR) RETURN VARCHAR
AS
BEGIN
RETURN REGEXP_COUNT(X, '^[0-9.-]+$');

HP Vertica

DB User:

• 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
• Set default search path
• Change Vertica User Privileges from Existing User to New user
• Execute Permissions on User Defined Functions For New Users
• Grant Execute Permissions on ODBC

Vertica Table

• Creation
• Rename Table, Table Column Or View in Vertica
• Projection Creation
• Create A New Table Using Existing Table
• Create Table Partitions
• Change the Owner of Table
• Drop Partition

Sequence:

• Create Sequence Number
• Alter Sequence
• Drop Sequence
• Add Sequence Or Set Default value to a table
• Drop Default Or Sequence on a Table
• Change the Owner of Sequence

Vertica Sessions

• Close Session in Vertica
• Currently Running Sessions in Vertica

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