Search code examples
sqlverticalistagg

Vertica LISTAGG not defined

As far as I can tell from the Vertica 8.1.x docs LISTAGG is supported for versions of Vertica 8.1.x and up; however, when I try to use this function I am seeing the following:

SQL Error [3457] [42883]: [Vertica]VJDBC ERROR: Function LISTAGG(varchar) does not exist, or permission is denied for LISTAGG(varchar)

While executing: 

SELECT LISTAGG(myColumn) FROM myTable;

I saw in another Stack Overflow post that LISTAGG was introduced in v9.1 (see here). I am currently using version v9.0.1-19

Can anyone confirm or provide insight as to why LISTAGG might not be working?

Solution

  • I can confirm it was added in version v9.1.1-4. See it in the v9.1.4 new features page

    I have 2 Vertica DBs, 1st with v9.0.1-5 and 2nd with v9.2.0-7. LISTAGG() is available on the 2nd but not on the 1st:

    dbadmin=> select version();
              version               
------------------------------------
 Vertica Analytic Database v9.0.1-5

dbadmin=> SELECT LISTAGG(myColumn) FROM myTable;
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

                  version               
------------------------------------
 Vertica Analytic Database v9.2.0-7

dbadmin=> SELECT LISTAGG(myColumn) FROM myTable;
 LISTAGG 
---------
 aaa,abc

    LISTAGG() is part of /opt/vertica/packages/VFunctions package, and it's the only function in the v_func schema in v9.1.1 and above.

    A good alternative is GROUP_CONCAT() which is part of string_packages UDx. See the readme for installation instructions and usage.