Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pWohl-000452-No for pgsql-general@arkaria.postgresql.org; Tue, 28 Feb 2023 01:22:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pWohk-0002Vl-Fj for pgsql-general@arkaria.postgresql.org; Tue, 28 Feb 2023 01:22:56 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pWohk-0002UA-51 for pgsql-general@lists.postgresql.org; Tue, 28 Feb 2023 01:22:56 +0000 Received: from evolvis.org ([217.144.135.11]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pWohf-0002jA-Ch for pgsql-general@lists.postgresql.org; Tue, 28 Feb 2023 01:22:55 +0000 Received: from x61w.mirbsd.org (xdsl-89-1-63-140.nc.de [89.1.63.140]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (Client did not present a certificate) by evolvis.org (Postfix) with ESMTPSA id 0F9A01002AE for ; Tue, 28 Feb 2023 01:22:48 +0000 (UTC) Received: by x61w.mirbsd.org (Postfix, from userid 1000) id 4BD7760B8E; Tue, 28 Feb 2023 02:22:47 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by x61w.mirbsd.org (Postfix) with ESMTP id 46E5860981 for ; Tue, 28 Feb 2023 02:22:47 +0100 (CET) Date: Tue, 28 Feb 2023 02:22:47 +0100 (CET) From: Thorsten Glaser To: pgsql-general@lists.postgresql.org Subject: Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!) In-Reply-To: Message-ID: References: <23d08470-aaa8-afca-dfd7-37261487a1f8@evolvis.org> Content-Language: de-DE-1901 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: QUOTED-PRINTABLE List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Mon, 27 Feb 2023, Tom Lane wrote: >Well, yeah. Simplify it to > > SELECT array_agg(DISTINCT x ORDER BY y) FROM mytable; That=E2=80=99s=E2=80=A6 a bit too simple for this case. >If there are several rows containing the same value of x and different >values of y, which y value are we supposed to sort the unique-ified x >value by? It's an ill-defined query. The problem here is that I do not have an =E2=80=98x=E2=80=99. >For the specific example you give, it's true that any specific >possible output of jsonb_build_object() would correspond to >a unique set of cot.weekday, cot.from_hour, cot.to_hour values. Not necessarily (see directly below), but why would that matter? It should sort the generated JSON objects within the array. >, or maybe thinking harder >about why you're getting duplicates in the first place. The application developer informed me that it=E2=80=99s entirely possible that some user entered matching information twice. I don=E2=80=99t have the exact query that produced duplicates easily in the history (the downside of working with \i) and tests on other users didn=E2=80=99t produce duplicates. So, yes, filtering them out is indeed part of the task here. On Mon, 27 Feb 2023, David G. Johnston wrote: >So long as the function call itself is at least stable...: > >DISTINCT func_call(...) ORDER BY func_call(...) aieee really? (I=E2=80=99d hope jsonb_build_object to be.) Is that better or worse than using the extra functions to sort=E2=80=A6? >Order By is evaluated AFTER and over the contents of the distinct-ified >expression That=E2=80=99s right and good, but the problem is that I do not seem to have a syntax with which to refer to the distinct-ified expression to use in the ORDER BY clause. >> place; I am somewhat proud I even managed to write this with >> JOINs and without any subqueries in the first place so I=E2=80=99m >> hesitant to go that route. > >That pride seems misplaced. Related to Tom's comment, the presence of the >DISTINCTs is telling you that what you did is not good. DISTINCT is almos= t >always a code smell Not about the DISTINCTs. I haven=E2=80=99t used JOIN much (usually WHERE as inner join) nor on tables this massive, and this is my second foray into aggregate functions only. >Lastly, if you do need to care about normalizing the output of JSON you >should consider writing a function that takes arbitrary json input and >reformats it, rather than trying to build up json from scratch where every >individual component needs to be aware and take action. i.e., get rid of >the ORDER BY also. Maybe this belongs in an application layer with toolin= g >that already provides this capability. Funnily enough, both here and in the other place where I tried to use JSON output, PostgreSQL (with COPY TO STDOUT) *is* the application layer. Here I=E2=80=99m generating a CSV file; in the other situation I was going to put the generated JSON directly into an HTTP result filehandle. In the latter, I dropped that approach, output CSV and converted that (by replacing newlines with =E2=80=9C],[=E2=80=9D and prepending =E2=80=9C[= [=E2=80=9D and appending =E2=80=9C]]=E2=80=9D) to JSON myself, which worked there as it was all-nume= ric. But the frustration there was about unnecessary whitespace instead. Both cases have in common that a, possibly huge, result set can be directly streamed from PostgreSQL to the consumer, but the former lacks just that tiny bit of functionality that would make it really rock :/ I was asking here because perhaps either that missing functionality can be considered, or to find out if there=E2=80=99s better ways to produce that output, due to my inexperience with SQL. The ways I=E2=80=99m using do work, and I=E2=80=99m relatively happy, but=E2=80=A6 bye, //mirabilos --=20 15:41=E2=8E=9C Somebody write a testsuite for hello= world :-)