Error - Arrays must contain only scalars and other arrays when array element is an array

From: Mike Martin <redtux1(at)gmail(dot)com>
To: pgsql-sql <pgsql-sql(at)lists(dot)postgresql(dot)org>
Subject: Error - Arrays must contain only scalars and other arrays when array element is an array
Date: 2020-08-03 10:39:11
Message-ID: CAOwYNKZq+jgWVTkPoUDThAi9n5gqq9arpbh_qAmUh0NZ2fA6BQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I have the interesting issue as per subject line

SOURCE data (perl DBD::Pg)

my $testdatasql=<<~'SQLSTR';
SELECT fileid,tagname,tagvalue--array_to_string(tagvalue,E'\b')
FROM tagdata_all
where tagvalue is not null
limit 100
SQLSTR
my $testdata=$dbh->selectall_arrayref($testdatasql);
my @testdata=(at){$testdata};

Then with unnest

my $testinssql2=<<~'SQLSTR';
TRUNCATE TABLE testunnest;
SELECT elem,nr FROM UNNEST ($1::text[][]) WITH ORDINALITY AS a(elem,nr)
SQLSTR
my $chk=$dbh->selectall_arrayref ($testinssql2,undef,\(at)testdata);

@testdata is a two-dimensional array with the second dimension being
int, text and text[] (array)

Any ideas?

Browse pgsql-sql by date

  From Date Subject
Next Message Samed YILDIRIM 2020-08-08 11:29:35 Re: Group by a range of values
Previous Message Steve Midgley 2020-08-01 18:55:47 Re: Group by a range of values