The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Partitioning - default partition ?


Im curious if i can manage partitions like in other dbs (postgres,edb etc.)...
I cannot find proper answer in docs and other sources so i will need your help...

My table looks like this:

count | potential_id_key
958800406 | 9
169481205 | 8
13249261 | 7
6070862 | 11
932035 | 22
184952 | 32
120417 | 10
37177 | 3
4090 | 5
395 | 19
227 | 14
192 | 2
99 | 33
48 | -200
18 | 21
6 | 1
1 | 23

So i believe, that the best option will be to partition by potential_id_key...but

Is is possible to create only 3 partitions i.e.
- partition_key=9
- partition_key=8
- partition_key=7
because those 3 keys contains biggest number of elements and other values store in DEFAULT partition?

If something like DEFAULT partition exists in HP VERTICA?


Im thinking about using projections as a workaround, but im just wondering if there is a simplest way to do this...





  •  Hi Kamil, 

    What you can do is make use of a derived default value for your partition_key column.


    See example:


    data INT,
    potential_id_key INT,
    partition_key VARCHAR(10) NOT NULL DEFAULT
    WHEN (abc.potential_id_key = 9)
    THEN 9
    WHEN (abc.potential_id_key = 8)
    THEN 8
    WHEN (abc.potential_id_key = 7)
    THEN 7
    ELSE 1



      - let`s say that your table has 2 columns and the third one "partition_key" is created just for the sole purpose of marking the partition location for that row of data based on the value of potential_id_key column.


     - every time you do an insert in the table the partition_key will be populated.


    Hope this helped ! 


  • Hi,


    Thanks for a quick reply...


    It sounds ok. It can be somekind of workaroud. I will have to check if this will have some influence on the performance...


    One more question.

    Do I need to change all projections order definitions? Im just wondering, because i remember (from official docs) that the order in projection should contains partition_key column if first place.


Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file