agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedUse multidimensional array as VALUES clause in insert
7+ messages / 6 participants
[nested] [flat]
* Use multidimensional array as VALUES clause in insert
@ 2020-08-11 10:47 Mike Martin <mike@redtux.plus.com>
0 siblings, 1 reply; 7+ messages in thread
From: Mike Martin @ 2020-08-11 10:47 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Use multidimensional array as VALUES clause in insert
@ 2020-08-11 10:50 Mike Martin <redtux1@gmail.com>
0 siblings, 3 replies; 7+ messages in thread
From: Mike Martin @ 2020-08-11 10:50 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Use multidimensional array as VALUES clause in insert
@ 2020-08-11 10:58 Samed YILDIRIM <samed@reddoc.net>
parent: Mike Martin <redtux1@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Samed YILDIRIM @ 2020-08-11 10:58 UTC (permalink / raw)
To: Mike Martin <redtux1@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
<div>Hi Mike,</div><div> </div><div>If you share sample data, expected results, table definitions etc, we can help you easier.</div><div> </div><div>Best regards.</div><div><br /></div><div><br /></div><div>İyi çalışmalar.</div><div>Samed YILDIRIM</div><div><br /></div><div><br /></div><div><br /></div><div>11.08.2020, 13:50, "Mike Martin" <redtux1@gmail.com>:</div><blockquote><div dir="ltr"><div class="37f99c05adf61ceede1004ece2f49ce5gmail-adn 169d1e978c5c4d4d6a75a6b7c218c676gmail-ads"><div class="650efa80f75db0b2195fdcc4923afc58gmail-gs"><div class="bb5d89171ed7877897c9a272e9d78c5agmail-"><div class="1ec776bb2c60f31cf3b77fb508a14d0gmail-ii 11216ae3590e6dd5d43796d9fd992875gmail-gt" id="7ab0c02bccd79d71ef59c2dbd9a48d56gmail-:3f4"><div class="d90934ce7dee1a85eb384021ac6d661gmail-a3s 2fbd98cc33c42d33822db7c52208dbeagmail-aXjCH" id="8802ded8e968a383deb0ee9b20c3121fgmail-:3f3"><div dir="ltr"><div>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.</div><div><br /></div><div>testing code</div><div><br /></div><div>--This gets me a multidimensional array</div><div>with arr AS (<br />SELECT ARRAY(SELECT ARRAY[fileid::text,tagname,array_to_string(tagvalue,E'\b')] <br /> FROM tagdata_all) -- limit 100)<br /> arr1<br />)<br /></div><div>--Then <br /></div><div><br /></div><div>INSERT INTO tagdatatest2<br />SELECT arr1::text[] FROM arr --doesnt work only populates one column with original array</div><div class="3eb4bdcb41c7ab91fd2d0450371e0d1fgmail-adL"><br /></div></div><div class="3eb4bdcb41c7ab91fd2d0450371e0d1fgmail-adL">
</div></div></div></div></div></div><div class="61c9831d9585c7a18443e96e6429c06egmail-gA 11216ae3590e6dd5d43796d9fd992875gmail-gt c39523941fb8cecf03823be54db4752gmail-acV"><div class="e102decbbeb89107bec6f7f6fc80132bgmail-gB 61c913a0e3e7d62619dfa1aa274d9c39gmail-xu"><div class="e6c9b6cacc9b9fe57024f2dc16a7f12bgmail-ip 7965e422231238917fdbfc9275430dc8gmail-iq"><div id="1ed286a026df8289e310eb311e1f8f9gmail-:3f5"><table class="f7bce0ca69367ddb6d8e6d58c53e6163gmail-cf 24d519d8e31b691ba21b9448b968c428gmail-wS"><tbody><tr><td class="7e5f8ec9a1b82e3458d84db39b4f3fdgmail-amq"><img class="d3315822366795bef65e4d3c0c8bbbbfgmail-ajn c41f32aa78d59352c60532229983a1fgmail-bofPge" id="626a6d07e37c19304b3243d9246f73a0gmail-:1q_82" src="https://www.google.com/s2/u/0/photos/public/AIbEiAIAAABDCPCpqeTPo7aPHyILdmNhcmRfcGhvdG8qKGQ0MmRlMGVi...; /></td><td class="5c6874502b687e5b3db6ab3f48617154gmail-amr"><br /></td></tr></tbody></table></div></div></div></div></div>
</blockquote>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Use multidimensional array as VALUES clause in insert
@ 2020-08-11 13:22 David G. Johnston <david.g.johnston@gmail.com>
parent: Mike Martin <redtux1@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2020-08-11 13:22 UTC (permalink / raw)
To: Mike Martin <redtux1@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On Tuesday, August 11, 2020, Mike Martin <redtux1@gmail.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
>
There is no simple way to have each element of an array be inserted into a
different column nor have each element populate a different row. You
should avoid the multi-dimensional array if at all possible.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Use multidimensional array as VALUES clause in insert
@ 2020-08-11 14:08 Thomas Kellerer <shammat@gmx.net>
parent: Mike Martin <redtux1@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: Thomas Kellerer @ 2020-08-11 14:08 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
Mike Martin schrieb am 11.08.2020 um 12:50:
> 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
I don't understand why you aggregate in the first place if you want to the insert the array elements as rows.
Can't you just do:
INSERT INTO tagdatatest2 (fileid, tagname, tagvalue)
SELECT fileid, tagname, array_to_string(tagvalue,E'\b')
FROM tagdata_all
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Use multidimensional array as VALUES clause in insert
@ 2020-08-11 14:43 Igor Andriychuk <2.andriychuk@gmail.com>
parent: Mike Martin <mike@redtux.plus.com>
0 siblings, 0 replies; 7+ messages in thread
From: Igor Andriychuk @ 2020-08-11 14:43 UTC (permalink / raw)
To: Mike Martin <mike@redtux.plus.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Hi Martin,
May be I don’t 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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Use multidimensional array as VALUES clause in insert
@ 2020-08-11 20:59 Mike Martin <redtux1@gmail.com>
parent: Thomas Kellerer <shammat@gmx.net>
0 siblings, 0 replies; 7+ messages in thread
From: Mike Martin @ 2020-08-11 20:59 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
The example isn't what I am doing, its just a test to get multidimensional
array within db for testing import of array from application (perl in this
case)
On Tue, 11 Aug 2020 at 15:08, Thomas Kellerer <shammat@gmx.net> wrote:
>
> Mike Martin schrieb am 11.08.2020 um 12:50:
> > 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
>
> I don't understand why you aggregate in the first place if you want to the
> insert the array elements as rows.
>
> Can't you just do:
>
> INSERT INTO tagdatatest2 (fileid, tagname, tagvalue)
> SELECT fileid, tagname, array_to_string(tagvalue,E'\b')
> FROM tagdata_all
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2020-08-11 20:59 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-08-11 10:47 Use multidimensional array as VALUES clause in insert Mike Martin <mike@redtux.plus.com>
2020-08-11 14:43 ` Igor Andriychuk <2.andriychuk@gmail.com>
2020-08-11 10:50 Use multidimensional array as VALUES clause in insert Mike Martin <redtux1@gmail.com>
2020-08-11 10:58 ` Samed YILDIRIM <samed@reddoc.net>
2020-08-11 13:22 ` David G. Johnston <david.g.johnston@gmail.com>
2020-08-11 14:08 ` Thomas Kellerer <shammat@gmx.net>
2020-08-11 20:59 ` Mike Martin <redtux1@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox