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 1pXr8C-0000yR-SB for pgsql-general@arkaria.postgresql.org; Thu, 02 Mar 2023 22:10:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pXr8B-0000bu-GC for pgsql-general@arkaria.postgresql.org; Thu, 02 Mar 2023 22:10:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pXr8B-0000bk-3m for pgsql-general@lists.postgresql.org; Thu, 02 Mar 2023 22:10:31 +0000 Received: from mail-pj1-x1034.google.com ([2607:f8b0:4864:20::1034]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pXr88-00034b-JJ for pgsql-general@lists.postgresql.org; Thu, 02 Mar 2023 22:10:30 +0000 Received: by mail-pj1-x1034.google.com with SMTP id h17-20020a17090aea9100b0023739b10792so442758pjz.1 for ; Thu, 02 Mar 2023 14:10:28 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1677795026; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=zVaaWobLdkflzcZE4sQxiiBwmISzxVr8BeVZ40vNflA=; b=JRnGMlIAd3abYAF256rVdqwBpo7MJ+4vpALiUZFrkgCnZXEEUywyT9J/3eZxvsXUAY 38cIQHvkBib1RqvYrTmb/Qs8ox/qylRx2CeXxi76gcYD/zlAal4QL2kEl5x2N31ONDk1 AlqUiicUReleDlGcFSXGniUP0PpFHl1AryqO1sxI7qSsIBwi6W1z4eons/zHATCnqGaH O6cJ5HWSUzkwzwyOVILmUzuaSdtgYgFlbznXsSeNyJ5Jj7XnrVZSZFBL93lcCjXdI3ua q8G086DdNtkdYnkFITdlkKTaPYYX0Q3QJWGOysqvFE1QwbkwJpXuu1lgs1Yd8JKZ9QYI 5/Fw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1677795026; h=in-reply-to:from:references: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=zVaaWobLdkflzcZE4sQxiiBwmISzxVr8BeVZ40vNflA=; b=5/C4QIF0dBw0KEh7+OesPpsqz/i30tCbrzSfLpm5H1j46v6Dt0iVY5R2OE6OoCdFno /CQrqGwUo4jUuB4dwQqtucgLvujDGEBySzzejYfBmdBf/MgW6IBaUzHAU3P05xojJFKC vrB2hy4TfZr+Vi7sc3dtzN9dAhGtd5LcMwkinuRDbvYQJVOdlA8s9XZahhukLol5GX88 Ws6R5gl0yAQBG8j+76fK/ErEN4neot+FccK5y4y0AaHiLfA9wdr3GK7i4efLCb3Ty2Rp I9QzrjWpTSaFFPtVz5aMEINwT9H3M9srYnx3foc9Q5sSyMd3c8doWbjJQWVa3o0FBo6L pwfw== X-Gm-Message-State: AO0yUKXxBkiayqzU6YI2cNyCB/SaNm9u0h0T4ZRzCwBSasMHIXv9NAL/ q12y9s8oXWZbH1BcJBIp0cS8AZZRlJq+4Q== X-Google-Smtp-Source: AK7set+m9eZq7qtqNZDnXTRRHwh1ORjK/iv25tMkzyt2KTXOntg1xz+ZuKh937OojxkJXgr9a5OsJQ== X-Received: by 2002:a05:6a20:3d05:b0:cc:7e9e:ad6c with SMTP id y5-20020a056a203d0500b000cc7e9ead6cmr4068942pzi.3.1677795025786; Thu, 02 Mar 2023 14:10:25 -0800 (PST) Received: from ?IPV6:2601:681:5a00:f790:6166:26b2:10a4:dd2e? ([2601:681:5a00:f790:6166:26b2:10a4:dd2e]) by smtp.gmail.com with ESMTPSA id i2-20020aa787c2000000b005b34d81b010sm181060pfo.91.2023.03.02.14.10.24 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 02 Mar 2023 14:10:25 -0800 (PST) Content-Type: multipart/alternative; boundary="------------vPagSoGZd9DChd1f0BKtSgE3" Message-ID: <660edb74-9a49-8d68-c489-7de99d955c60@gmail.com> Date: Thu, 2 Mar 2023 15:10:24 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.7.1 Subject: Re: Converting row elements into a arrays? Content-Language: en-CA To: pgsql-general@lists.postgresql.org References: <788ab733-d0d1-7791-fb22-7c2724b08816@gmail.com> <36f53642-db43-4fc9-028e-8b6cd3dff9b4@gmail.com> From: Rob Sargent In-Reply-To: <36f53642-db43-4fc9-028e-8b6cd3dff9b4@gmail.com> 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. --------------vPagSoGZd9DChd1f0BKtSgE3 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 3/2/23 14:49, Ron wrote: > On 3/2/23 15:45, Rob Sargent wrote: >> On 3/2/23 13:58, Ron wrote: >>> Postgresql 12.13 >>> >>> Given the sample below, I'm looking for how to generate this >>> output.  It's like GROUP BY, but generating an array instead of an >>> aggreate number. >>>  f1 | f2_array >>> ----+--------- >>> 1 | {1,2,3} >>>   2 | {1,2,3,4} >>>   3 | {1,2} >>> >>> The ultimate goal is to somehow use pg_index.indkey to get column >>> names from pg_attribute. >>> >>> create table foo (f1 int, f2 int); >>> insert into foo values (1, 1); >>> insert into foo values (1, 2); >>> > [snip] >> >> In which environment are you accessing that array?  psql only? > > Correct. > For multiple columns it's a litte messier ( if you have different data types in your columns) create table foo (f1 int, f2 int, f3 text); insert into foo values (1, 1,'asdf'); insert into foo values (1, 2, 'qerr'); select f1, array_agg(array[f2::text,f3]) from foo group by f1;  f1 |      array_agg ----+---------------------   1 | {{1,asdf},{2,qerr}} (1 row) --------------vPagSoGZd9DChd1f0BKtSgE3 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 3/2/23 14:49, Ron wrote:
On 3/2/23 15:45, Rob Sargent wrote:
On 3/2/23 13:58, Ron wrote:
Postgresql 12.13

Given the sample below, I'm looking for how to generate this output.  It's like GROUP BY, but generating an array instead of an aggreate number.
 f1 | f2_array
----+---------
1 | {1,2,3}
  2 | {1,2,3,4}
  3 | {1,2}

The ultimate goal is to somehow use pg_index.indkey to get column names from pg_attribute.

create table foo (f1 int, f2 int);
insert into foo values (1, 1);
insert into foo values (1, 2);

[snip]

In which environment are you accessing that array?  psql only?

Correct.


For multiple columns it's a litte messier ( if you have different data types in your columns)

create table foo (f1 int, f2 int, f3 text);
insert into foo values (1, 1,'asdf');
insert into foo values (1, 2, 'qerr');

select f1, array_agg(array[f2::text,f3]) from foo group by f1;
 f1 |      array_agg      
----+---------------------
  1 | {{1,asdf},{2,qerr}}
(1 row)

--------------vPagSoGZd9DChd1f0BKtSgE3--