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 1pLWE8-0006FY-VJ for pgsql-general@arkaria.postgresql.org; Fri, 27 Jan 2023 21:25:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pLWE7-0007gQ-Nd for pgsql-general@arkaria.postgresql.org; Fri, 27 Jan 2023 21:25:39 +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 1pLWE7-0007cv-8r for pgsql-general@lists.postgresql.org; Fri, 27 Jan 2023 21:25:39 +0000 Received: from mail-pj1-x1033.google.com ([2607:f8b0:4864:20::1033]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pLWE3-0002FM-6B for pgsql-general@lists.postgresql.org; Fri, 27 Jan 2023 21:25:38 +0000 Received: by mail-pj1-x1033.google.com with SMTP id h5-20020a17090a9c0500b0022bb85eb35dso5919136pjp.3 for ; Fri, 27 Jan 2023 13:25:34 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:from:to:cc:subject:date :message-id:reply-to; bh=BjXTsPymRras63KSTSk73L4k9LtAxOYA4pI4zSiZ9vw=; b=H2ubw1PjfvZPbyqJ3w2Cvl212QBZeCDPXJMvL52Nk/L6bB02Gs699BymaizZP34ozI I6k6o/7TVSvoultr3v1mD7R/WigfoYfV2HcsAI2ChqrmAW39Ko4pbeulymHzvWb8WuLM tfwpKZTFn5JVMy3y6kBo938MDKC41fVj6CegQcyl9k7aAGds7Pb89qF27ljXShJ8DMJJ MN+kPzMljW8DHz92l3jBnYpsa/wzDihZsa3z13+53jenIe0UhC3R/a7YcYXuQmI8yEUT QNKD6V39fQUFpNGLpJY1hWVqRsf3n/IkjzPRykx6Ai1nFmPz3fOBSTcP86KMBHLrYrCx Iwfg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=BjXTsPymRras63KSTSk73L4k9LtAxOYA4pI4zSiZ9vw=; b=Z+WDtO1Z7bfsyV/ZA05bM9W+0l+HAb7AH/ATYsnv0QmWPo8DI2oPKmQuhdMHH3IDk1 gfX2qhH9nRdp1fZxQgDIMewf30ykdmU43YPM+y10gR4H79v911UWSOsEYIknEQvHcvHJ TXw0TjPA5U8U3298fIYtdF0xjrdv3MT/y3iz7szEMrz5eOPfGxvEHYKX1ozEVn3S32YJ E1MfS7UGcUXTUsSTnVn+f3HZF3D+8F43/DSLpaugOfwsXmAIG+s8OwLxH51cy69ux66Q 3U4NoUcS+T3Hk4o+zzgxeRoPkGBVAFBN7gVLB1YQ8+2saij0elP+zgPLRY0di+6QM8yr c4Vw== X-Gm-Message-State: AFqh2krc8I6StC6uUomyVlDgtS/IkP2XV/SgouiaR/5A2Ux9Zzd5DLaZ s1Zk0E4ouBbOFAd/uZLA4HY= X-Google-Smtp-Source: AMrXdXul1NxRn/bx2SqMu5h93wpQO2CmoaBe26SRJBc4s2BsHKp8wB1y9XjLUH4W1f/io4DulfmEhw== X-Received: by 2002:a17:903:1212:b0:195:f3e0:769a with SMTP id l18-20020a170903121200b00195f3e0769amr30123622plh.69.1674854733749; Fri, 27 Jan 2023 13:25:33 -0800 (PST) Received: from ?IPV6:2601:681:5a00:f790:fea0:6247:5eca:cde2? ([2601:681:5a00:f790:fea0:6247:5eca:cde2]) by smtp.gmail.com with ESMTPSA id r19-20020a170902c61300b0018544ad1e8esm3283116plr.238.2023.01.27.13.25.32 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 27 Jan 2023 13:25:33 -0800 (PST) Content-Type: multipart/alternative; boundary="------------19tSjhPZjLyadGw8KOkdLmDZ" Message-ID: <68161326-65af-ee8b-0c9e-be5d8f0b4f7b@gmail.com> Date: Fri, 27 Jan 2023 14:25:32 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: nextval per counted Content-Language: en-CA To: "David G. Johnston" Cc: "pgsql-generallists.postgresql.org" References: <71aa4ae1-f789-485a-ae58-2acec9aa2df0@gmail.com> From: Rob Sargent In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------19tSjhPZjLyadGw8KOkdLmDZ Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 1/27/23 14:20, David G. Johnston wrote: > On Fri, Jan 27, 2023 at 1:59 PM Rob Sargent wrote: > > I'm trying to craft SQL to invoke a sequence nextval once per > grouped value. > > > This seems like a very unusual usage of nextval/sequences... > > with cleanup as ( >   select DISTINCT e.ma , coalesce(e.pa , > 'fix') as pa from ... > ), compute as ( >   select ma, pa, nextval(...) from cleanup > ) > select * from compute ... -- do whatever else you want > > So far I have this: > > with husb as( > select e.ma , count(distinct coalesce(e.pa > , nextval('egogen')::text)) as mates > from emp_all_by3 e group by e.ma order by mates > ) > select mates, count(*) > from husb > group by mates order by mates desc; > > Your "order by mates" in the CTE is totally pointless and wasting > resources. > > David J. > Yeah, it wasn't when that was the last bit executed... Seems asking a sequence for an id isn't too unusual?  Or are they specifically intended for primary keys? --------------19tSjhPZjLyadGw8KOkdLmDZ Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 1/27/23 14:20, David G. Johnston wrote:
On Fri, Jan 27, 2023 at 1:59 PM Rob Sargent <robjsargent@gmail.com> wrote:
I'm trying to craft SQL to invoke a sequence nextval once per grouped value.


This seems like a very unusual usage of nextval/sequences...

with cleanup as (
  select DISTINCT e.ma, coalesce(e.pa, 'fix') as pa from ...
), compute as (
  select ma, pa, nextval(...) from cleanup
)
select * from compute ... -- do whatever else you want

So far I have this:

with husb as(
select e.ma, count(distinct coalesce(e.pa, nextval('egogen')::text)) as mates
from emp_all_by3 e group by e.ma order by mates
)
select mates, count(*)
from husb
group by mates order by mates desc;
Your "order by mates" in the CTE is totally pointless and wasting resources.

David J.

Yeah, it wasn't when that was the last bit executed...

Seems asking a sequence for an id isn't too unusual?  Or are they specifically intended for primary keys?

--------------19tSjhPZjLyadGw8KOkdLmDZ--