How to handle Vertica Columns having Dot(.)

Amit_1

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

 

 

 

 

Said_Montiel

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.