The Vertica Forum is getting a makeover! The site will be undergoing maintenance from Tuesday 8/13 to Friday 8/16. We appreciate your patience and cooperation during this time.

Create custom superprojection does not take into account column encodings from CREATE TABLE

CREATE TABLE a ( 
data INTEGER NOT NULL 
); 
CREATE PROJECTION a_custom_super AS SELECT * from a; 
INSERT INTO a VALUES (123); 

SELECT EXPORT_OBJECTS('','a'); 

outputs 

CREATE TABLE mab_test.a 

data int NOT NULL 
); 



CREATE PROJECTION mab_test.a_custom_super 

data 

AS 
SELECT a.data 
FROM mab_test.a 
ORDER BY a.data 
UNSEGMENTED NODE v_verticatest_node0001; 


SELECT MARK_DESIGN_KSAFE(0); 


But when we specify encodings for columns in the original CREATE TABLE statement, a default super projection 'mab_test.a_super' is created instead of specifying this encoding for my custom superprojection. 

DROP TABLE a cascade; 
CREATE TABLE a ( 
data INTEGER NOT NULL ENCODING RLE 
); 
CREATE PROJECTION a_custom_super AS SELECT * from a; 
INSERT INTO a VALUES (123); 

SELECT EXPORT_OBJECTS('','a'); 

outputs 

CREATE TABLE mab_test.a 

data int NOT NULL 
); 



CREATE PROJECTION mab_test.a_super /*+basename(a),createtype(P)*/ 

data ENCODING RLE 

AS 
SELECT a.data 
FROM mab_test.a 
ORDER BY a.data 
SEGMENTED BY hash(a.data) ALL NODES ; 

CREATE PROJECTION mab_test.a_custom_super 

data 

AS 
SELECT a.data 
FROM mab_test.a 
ORDER BY a.data 
UNSEGMENTED NODE v_verticatest_node0001; 


SELECT MARK_DESIGN_KSAFE(0);

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.