Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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:

 

temp.source

temp.email

temp.name

temp.id

 

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

 

Ex:

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.

 

Thanks,

-Amit

 

 

 

 

Comments

  • 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
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.