How to handle Vertica Columns having Dot(.)

Hi All,


We have a requirement where we are building Vertica Flex tables from JSON files. As its automatically getting created we have few column names like:







Now I am not able to select those columns directly using the column names, Ex:

Select temp.source,temp.email,temp.name, temp.id from temp_table;


YThe above statement gives me error saying, column temp does not exit. I know its because of Dot(.) present in between the column name. Now if I put the column names with in quote(" ") like below, I am not getting that error



Select "temp.source","temp.email","temp.name", "temp.id" from temp_table;


Is there any other way of handling this without adding quotes or updating the column names.

Any help would be much appreciated.









  • Options

    A couple of ideas


    1) Loading change

    Depending on the method you are using to load data you may be able to change the column names during transmission. I read something in Vertica 6.1 documentation, but I can't find the section.


    2) Mask it

    If  you create a VIEW you can "mask" the column names, but I do not recommend this route because even if you find an efficient way to create the VIEW you will always have an overhead that should not be there.


    3) Change current structure

    Create a new table with appropiate names. Change the loading function to point to the new table with corrected names, and copy the data from the old table to the new one.

Leave a Comment

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