Postgres Pro
Downloads
Home   >   mailing lists

[HACKERS] ToDo: listagg is in ANSI/SQL:2016 - Mailing list pgsql-hackers

From Pavel Stehule
Subject [HACKERS] ToDo: listagg is in ANSI/SQL:2016
Date
Msg-id CAFj8pRD=BR0JM8NFVpFsaY3ztvEupwhSKgDkHLCRLjpCMM1RAA@mail.gmail.com
Whole thread Raw
List pgsql-hackers
Tree view
Hi

looks like Oracle has pretty strong position in standard. ANSI SQL has new aggregate function listagg. It is supported by DB2 too.

Unfortunately one supported syntax is not possible in Postgres due our design of ordered aggregates.

Syntax:

1. listagg(expr) FROM ... not deterministic result
2. listagg(expr, separator) FROM ... previous with separator - when sep. is NULL, then it is ignored

3. listagg(expr [, sep]) WITHIN GROUP (ORDER BY expr_list)

last syntax is not available in Postgres - because our ordered aggregates expects immutable arguments in ordered aggregates arguments. First two are not supported two, because ORDER BY clause is required every time.

Regards

Pavel


pgsql-hackers by date:

Previous
From: Ashutosh Sharma
Date:
Subject: Re: [HACKERS] PATCH: pageinspect / add page_checksum and bt_page_items(bytea)
Next
From: David Rowley
Date:
Subject: Re: [HACKERS] Performance improvement for joins where outer side is unique