Transforms non-null values from a group of rows into a list of values that are delimited by a configurable separator. LISTAGG can be used to denormalize rows into a string of comma-separated values or other human-readable formats.

Behavior Type



LISTAGG ( [ DISTINCT ] column‑expression 
          [ USING PARAMETERS [, max_length=integer-expr] 
                             [, separator='string'] 
                             [, on_overflow={'ERROR'|'TRUNCATE'}] ] )



A table column or column expression to select from the source table or view.

LISTAGG does not support spatial data types directly. In order to pass column data of this type, convert the data to strings with the geospatial function ST_AsText.

Converted spatial data frequently contains commas. LISTAGG uses comma as the default separator character. To avoid ambiguous output, override this default by setting the function's separator parameter to another character.


Parameter name Set to…

An integer or integer expression that specifies in bytes the maximum length of the result, up to 32M.

Default: 1024


Separator string of length 0 to 80, inclusive. A length of 0 concatenates the output (no separator).

Default: Comma (,)


Specifies behavior when the result overflows the max_length setting, one of the following strings:

  • ERROR (default): Return an error when overflow occurs.
  • TRUNCATE: Remove any characters that exceed max_length setting from the query result, and return the truncated string.




In this example, the results in the CityState column use the string " | " as a separator and are truncated if they exceed 80 characters in length:

=> SELECT customer_region Region, 
    LISTAGG (DISTINCT customer_city||', '||customer_state USING PARAMETERS max_length=80,
    separator=' | ', on_overflow='TRUNCATE') CityAndState FROM customer_dimension GROUP BY Region;
  Region   |                                   CityAndState
 West      | San Diego, CA | Norwalk, CA | Berkeley, CA | West Covina, CA | Costa Mesa, CA |
 South     | Savannah, GA | Lafayette, LA | Fort Worth, TX | Carrollton, TX | Clearwater, FL
 SouthWest | Gilbert, AZ | Topeka, KS | Denver, CO | Westminster, CO | Pueblo, CO | Las Vegas
 East      | Columbia, SC | Hartford, CT | Waterbury, CT | Portsmouth, VA | Washington, DC |
 MidWest   | Milwaukee, WI | Ann Arbor, MI | Evansville, IN | Peoria, IL | Joliet, IL | Detro
 NorthWest | Seattle, WA | Portland, OR | Bellevue, WA
(6 rows)