Use multidimensional array as VALUES clause in insert

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Use multidimensional array as VALUES clause in insert

Mike Martin
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


Reply | Threaded
Open this post in threaded view
|

Re: Use multidimensional array as VALUES clause in insert

Samed YILDIRIM
Hi Mike,
 
If you share sample data, expected results, table definitions etc, we can help you easier.
 
Best regards.


İyi çalışmalar.
Samed YILDIRIM



11.08.2020, 13:50, "Mike Martin" <[hidden email]>:
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


Reply | Threaded
Open this post in threaded view
|

Re: Use multidimensional array as VALUES clause in insert

David G Johnston
In reply to this post by Mike Martin
On Tuesday, August 11, 2020, Mike Martin <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Use multidimensional array as VALUES clause in insert

Thomas Kellerer-4
In reply to this post by Mike Martin

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



Reply | Threaded
Open this post in threaded view
|

Re: Use multidimensional array as VALUES clause in insert

Mike Martin
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 <[hidden email]> 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