[SOLVED] How to do range partitioning on varchar column in vertica

How to do range partitioning on varchar column in vertica

I have a big table and want to do partitioning on varchar columns.I have tried to partitioning it using this script in vertical:

create table tb1(
symbol varchar not null,
...
mmid varchar)
PARTITION BY symbol;

I believe that the PARTITION BY did value partitioning on symbol column, and when I load data into the table, it failed with too many partitions as expected.

How can I do range partitioning on symbol column?

For example I know that the DolphinDB could do this by using the below script

sym = `a`abc`aaa`bbc`bac`b`c`cd`cab`abd
val = rand(1.0, 10)
t=table(sym, val)
db=database("/tmp/db", RANGE, `a`b`c`d)
db.createPartitionedTable(t, `table, `sym)

the patitions will be a-b b-c and c-d.

Solution

You can use any deterministic function in the PARTITION BY clause.

For example:

CREATE TABLE tb1 (
  symbol varchar NOT NULL
) PARTITION BY LEFT(symbol,2);

INSERT /*+direct*/ INTO tb1 SELECT 'abc';
INSERT /*+direct*/ INTO tb1 SELECT 'bbc';
INSERT /*+direct*/ INTO tb1 SELECT 'bca';

SELECT DISTINCT partition_key
FROM partitions
WHERE projection_name LIKE 'tb1%';

 partition_key 
---------------
 ab
 bb
 bc