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='char'] 
                             [, 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.

Parameter Settings

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

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

=> SELECT customer_region Region, 
   LISTAGG (DISTINCT customer_city||customer_state USING PARAMETERS max_length=80, on_overflow='TRUNCATE') CityState
   FROM customer_dimension GROUP BY Region;
  Region   |                                    CityState
-----------+----------------------------------------------------------------------------------
 West      | Simi ValleyCA,InglewoodCA,Daly CityCA,VallejoCA,LancasterCA,FullertonCA,Sunnyval
 NorthWest | BellevueWA,PortlandOR,SeattleWA
 MidWest   | IndianapolisIN,PeoriaIL,JolietIL,NapervilleIL,South BendIN,MilwaukeeWI,Green Bay
 South     | ClearwaterFL,Cape CoralFL,El PasoTX,MesquiteTX,McAllenTX,AthensGA,San AntonioTX,
 SouthWest | TopekaKS,PeoriaAZ,Fort CollinsCO,DenverCO,PuebloCO,PhoenixAZ,WestminsterCO,North
 East      | ManchesterNH,StamfordCT,CambridgeMA,EriePA,ElizabethNJ,ColumbiaSC,WashingtonDC,F
(6 rows)