Vertica Quick Tip: Projection Create Types

Posted April 2, 2018 by Soniya Shah, Information Developer

This blog post was authored by Jim Knicely. The DDL produced by the EXPORT_OBJECTS function typically contains a create type for a projection. Example: CREATE PROJECTION public.test_super /*+basename(test),createtype(L)*/ ( c1 ) AS SELECT test.c1 FROM public.test ORDER BY test.c1 SEGMENTED BY hash(test.c1) ALL NODES OFFSET 0; In the above example the create type is L for “Lazy”, meaning that I inserted data into the table when no projections existed yet. Here is a list of the projection create types you’ll encounter along with the Vertica method used to create them: U - CREATE PROJECTION A - CREATE TABLE L - DELAYED CREATION T - IMPLEMENT_TEMP_DESIGN C - DESIGNER CANDIDATE D - DESIGNER S - SYSTEM TABLE P - CREATE TABLE WITH PROJ CLAUSE N - ALTER TABLE Have Fun!