Postgres Pro
Downloads
Home   >   mailing lists

Re: DISTINCT in STRING_AGG - Mailing list pgsql-general

From Sterpu Victor
Subject Re: DISTINCT in STRING_AGG
Date
Msg-id em97584d9f-92b4-4097-b6a2-1f346c685359@victor-pc
Whole thread Raw
In response to Re: DISTINCT in STRING_AGG  (Geoff Winkless <pgsqladmin@geoff.dj>)
List pgsql-general
Tree view
Thank you.
 
I think there is no native function that will solve this problem, intarray extension ​can't order as in the example query.
I could write a new SQL function but I solved the problem with subqueries and works fine.
 
I need order because I use nested trees.
To_left and to_right are the coordinates for each node and the result must be ordered by this to obtain the correct result. 
 
 
 
 
------ Original Message ------
From: "Geoff Winkless" <pgsqladmin@geoff.dj>
To: "Sterpu Victor" <victor@caido.ro>
Cc: "PostgreSQL General" <pgsql-general@postgresql.org>
Sent: 11/29/2015 10:51:32 PM
Subject: Re: Re[2]: [GENERAL] DISTINCT in STRING_AGG
 
On 29 November 2015 at 18:59, Sterpu Victor <victor@caido.ro> wrote:
I can't skip the ordering.
I'm sure aqjs3 is the one that produces the duplication.
I guess subqueries are the only option, like this:

​Well you could look at the intarray ​extension and a combination of array_agg, uniq() and string_to_array:

http://www.postgresql.org/docs/current/static/intarray.html

but that's probably sledgehammer:nut time.

Not sure why you need to order the values you're getting back by something other than the values themselves - is there a reason you wouldn't want the "children"​ set to be ordered numerically? You can still order the outer query by whatever you like, or you can order the aggregate by the values themselves, it's just the DISTINCT inside the aggregate query requires that an internal ORDER includes the ordering term in the result.

​Geoff​


DISCLAIMER:
Acest mesaj de posta electronica si documentele aferente sunt confidentiale. Este interzisa distribuirea, dezvaluirea sau orice alt mod de utilizare a lor. Daca nu sunteti destinatarul acestui mesaj, este interzis sa actionati in baza acestor informatii. Citirea, copierea, distribuirea, dezvaluirea sau utilizarea in alt mod a informatiei continute in acest mesaj constituie o incalcare a legii. Daca ati primit mesajul din greseala, va rugam sa il distrugeti, anuntand expeditorul de eroarea comisa. Intrucat nu poate fi garantat faptul ca posta electronica este un mod sigur si lipsit de erori de transmitere a informatiilor, este responsabilitatea dvs. sa va asigurati ca mesajul (inclusiv documentele alaturate lui) este validat si autorizat spre a fi utilizat in mediul dvs.

pgsql-general by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: Problems with pg_upgrade after change of unix user running db.
Next
From: Benedikt Grundmann
Date:
Subject: Re: Problems with pg_upgrade after change of unix user running db.