The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
How to read array data into one column
I have a table (tbl1) where a column is used as array data:
I would like to read this data so it shows ID|email as columns:
Also, I would like to know how to read it back from a list (pic2) to an array (pic1).
0
Answers
Please review the below link.. It should help
https://www.vertica.com/blog/vertica-quick-tip-dynamically-split-string/
For the first request, you can also use the StringTokenizerDelim function:
.
.
.
For the second request, you can use the ListAgg function:
.
.
Thank you SruthiA and LenoyJ! The first part worked great using both your ideas. I have issue with second part - combining list structure back into array: ERROR: Function listagg(varchar) does not exist, or permission is denied for listagg(varchar)
Are those user created functions?
Whoops, I should've mentioned that ListAgg was introduced in 9.1.1-4.
If you can't upgrade - you can try using Group_Concat function instead from the Strings package on our github. You'll have to build it from source for your platform though. Instructions here:
https://github.com/vertica/Vertica-Extension-Packages/tree/master/strings_package
Example on how to use:
https://github.com/vertica/Vertica-Extension-Packages/blob/master/strings_package/examples/group_concat.sql
Thanks for the tip! We will be upgrading most likely next year, but I will try with Group_Concat.