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 access LISTAGG or GROUP_CONCAT in Vertica 9.3?
How to install and access LISTAGG or GROUP_CONCAT in Vertica 9.3? I'm under the impression the LISTAGG is standard in 9.2 and the .cpp files were available in 7.1.
=> SELECT VERSION(); VERSION ------------------------------------ Vertica Analytic Database v9.3.1-8 (1 row) => SELECT manufact, LISTAGG(model) FROM test.car_sales GROUP BY manufact; ERROR 3457: Function LISTAGG(varchar) does not exist, or permission is denied for LISTAGG(varchar) HINT: No function matches the given name and argument types. You may need to add explicit type casts => SELECT manufact, GROUP_CONCAT(model) FROM test.car_sales GROUP BY 1; ERROR 3457: Function GROUP_CONCAT(varchar) does not exist, or permission is denied for GROUP_CONCAT(varchar) HINT: No function matches the given name and argument types. You may need to add explicit type casts
0
Best Answer
-
Ariel_Cary Vertica Employee Employee
LISTAGG doesn't need special permissions. PUBLIC has execution privilege. Try running this query to verify the functionality is installed
vsql -f /opt/vertica/packages/VFunctions/ddl/isinstalled.sql
If it's not, you can re-install it with admintools.0
Answers
LISTAGG
was introduced in 9.1.1-4. Make sure the user you're using has appropriate permissions to access the function. You can try with dbadmin to test and eliminate permission issues (if any).https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/LISTAGG.htm
@Ariel_Cary Thanks! Installing VFunctions worked.
admintools -t install_package -d protobi_db -P VFunctions --force-reinstall -p $DATABASE_PWD