LISTAGG

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

Volatile

Syntax

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

Arguments

column‑expression

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.

Parameters

Parameter name Set to…
max_length

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

Default: 1024

separator

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

Default: Comma (,)

on_overflow

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.

Privileges

None

Examples

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)