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 1k5VUe-0004pl-7g for pgsql-sql@arkaria.postgresql.org; Tue, 11 Aug 2020 14:43:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1k5VUd-0003Nq-3o for pgsql-sql@arkaria.postgresql.org; Tue, 11 Aug 2020 14:43:11 +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 <2.andriychuk@gmail.com>) id 1k5VUc-0003Nj-S8 for pgsql-sql@lists.postgresql.org; Tue, 11 Aug 2020 14:43:10 +0000 Received: from mail-pf1-x430.google.com ([2607:f8b0:4864:20::430]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from <2.andriychuk@gmail.com>) id 1k5VUa-0006cR-KH for pgsql-sql@lists.postgresql.org; Tue, 11 Aug 2020 14:43:10 +0000 Received: by mail-pf1-x430.google.com with SMTP id m8so7733566pfh.3 for ; Tue, 11 Aug 2020 07:43:08 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=So5vstcYHMgua4sqoEp3Dfw7gL+PfzlOPdnxuXaJzmA=; b=pfMPO7CEavvyPfEgIP+Mpsv7uEBdHJumIXocM5GiDWBL4hEwZ/XEhncFtg00mVN5C1 YozmpE3elFA4Y/N8kFiaP5AsFHJClxewwDtBxnJLu0aGkmgbXkRF8VQGf55dKA3imrrE fy10DPDiTntKsUDOUKoPqirvWZfJK4jEWBr6V58PTudjdh4FWi3KBe0aF6H+G3qlqcRM +Ayy3EFxdQO7nGF5v5ObbvjIs9+EG/J7DUKP3fbkDS9yYc0csIs26N8ExhIZ8REKaJzY xaOCRrIXK7mmfwW08tH8U9K/X/ij6Uuk3sGUexxNOxfVU1Y21UKHu4xgVNrT3fFMzwDW 9hrA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=So5vstcYHMgua4sqoEp3Dfw7gL+PfzlOPdnxuXaJzmA=; b=q/XaTxd2YhnfGmGX+0qWPZCmRIctLLQ1ApKoAFsujm9xICvr5FLqBp589SKnLZ/Gjs r5Y3L1fjdzvk/wDwk7bJDUtBsKSV0Q2a9WLFVYqNuDQFt11m2EZaALjBjrEi/jjZhUrn Ova3MRi8IvZWiaVaRV105NZZHgICDXPc6Pp19FFFwwWj9gbPJeGEYEQfTPzXD7R52nVU zW5NDxWsUUUiWaFOEo+M7Ew9ZnQr154/3SLk++EGJQ4NmCZdJcGo+r6CEIWG3EjRdjDR tjuU6OPJPfxWx0emgDinRq+6xmFvVpgp2YMq+YDMsq6xpca0ITIo9FdHZyGmd5JO6bLa bwYg== X-Gm-Message-State: AOAM530xVJReKQdkLq/+vlKKkWel3BuxblxstMLdZkX8U8mVJge1C9lr Sl/6kdNd4nJ63Tqh5NUDsN8Yms3NIXY= X-Google-Smtp-Source: ABdhPJyVYE83l3v3dfm55n19u3FBTiG3aZsTJwK3HevZupSuHeKdTobt2bJ6YlBrmtTP0vXmNrXQrA== X-Received: by 2002:a63:e258:: with SMTP id y24mr1061008pgj.434.1597156986439; Tue, 11 Aug 2020 07:43:06 -0700 (PDT) Received: from [192.168.1.19] (198-27-163-68.fiber.dynamic.sonic.net. [198.27.163.68]) by smtp.gmail.com with ESMTPSA id g9sm25861227pfr.172.2020.08.11.07.43.05 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 11 Aug 2020 07:43:05 -0700 (PDT) From: Igor Andriychuk <2.andriychuk@gmail.com> Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_9F57FC41-1E6C-4924-BD55-6267FE6DE2A0" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.1\)) Subject: Re: Use multidimensional array as VALUES clause in insert Date: Tue, 11 Aug 2020 07:43:04 -0700 In-Reply-To: Cc: pgsql-sql To: Mike Martin References: X-Mailer: Apple Mail (2.3608.120.23.2.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_9F57FC41-1E6C-4924-BD55-6267FE6DE2A0 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hi Martin, May be I don=E2=80=99t understand completely what are you trying to = accomplish but based on your example bellow UNNEST function should do a = magic for you, this is the example I wrote for your case: create table if not exists foo(id int, arr text[]); truncate table foo; insert into foo values (1, '{"value1", "value2", "value3"}'), (2, = '{"value21", "value22", "value23"}'); create table if not exists foo2(id int, val text); truncate table foo2; insert into foo2 select id, unnest(arr) from foo;=20 Is this what you trying to do? Best, -Igor > On Aug 11, 2020, at 3:47 AM, Mike Martin wrote: >=20 > Is this possible? I have seen examples with array literals as VALUES = string, but I cant seen to get it to work with an actual array. >=20 > testing code >=20 > --This gets me a multidimensional array > with arr AS ( > SELECT ARRAY(SELECT = ARRAY[fileid::text,tagname,array_to_string(tagvalue,E'\b')]=20 > FROM tagdata_all) -- limit 100) > arr1 > ) > --Then=20 >=20 > INSERT INTO tagdatatest2 > SELECT arr1::text[] FROM arr --doesnt work only populates one column = with original array >=20 --Apple-Mail=_9F57FC41-1E6C-4924-BD55-6267FE6DE2A0 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Hi = Martin,

May be I = don=E2=80=99t understand completely what are you trying to accomplish = but based on your example bellow UNNEST function should do a magic for = you, this is the example I wrote for your case:


create table= if not exists foo(id int, arr text[]);
truncate table= foo;
insert into foo values (1,= '{"value1", "value2", "value3"}'), (2, '{"value21", "value22", "value23"}');

create table if not exists foo2(id= int, val text);
truncate table= foo2;

insert into = foo2
select id, unnest(arr) from foo; 


Is = this what you trying to do?

Best,
-Igor



On Aug = 11, 2020, at 3:47 AM, Mike Martin <mike@redtux.plus.com> wrote:

Is this possible? I have seen examples with = array literals as VALUES string, but I cant seen to get it to work with = an actual array.

testing code

--This gets me a multidimensional array
with arr AS (
SELECT ARRAY(SELECT = ARRAY[fileid::text,tagname,array_to_string(tagvalue,E'\b')]
FROM tagdata_all) -- limit 100)
arr1
)
--Then

INSERT INTO  tagdatatest2
SELECT =  arr1::text[] FROM arr --doesnt work only populates one column with = original array


= --Apple-Mail=_9F57FC41-1E6C-4924-BD55-6267FE6DE2A0--