How to access LISTAGG or GROUP_CONCAT in Vertica 9.3?

Pieter_Sheth-Vo · November 2020

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

Ariel_Cary · November 2020

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.

LenoyJ · November 2020

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

dbadmin=> select id, listagg(name) ag from mytable group by 1;
 id |    ag
----+----------
  1 | AA
  2 | AB,AC
  3 | AF,AE,AD

https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/LISTAGG.htm

Pieter_Sheth-Vo · November 2020

@Ariel_Cary Thanks! Installing VFunctions worked.
admintools -t install_package -d protobi_db -P VFunctions --force-reinstall -p $DATABASE_PWD

We're Moving!

Create My New Community Account Now

How to access LISTAGG or GROUP_CONCAT in Vertica 9.3?

Best Answer

Answers

Leave a Comment