BUG #6326: SELECT ARRAY(SELECT ...) syntax and array[] results doesn't work
The following bug has been logged on the website:
Bug reference: 6326
Logged by: Maksym Boguk
Email address: maxim.boguk@gmail.com
PostgreSQL version: 9.1.1
Operating system: Linux
Description:
SELECT ARRAY(SELECT ...)
doesn't work when subselect return any array.
Test case:
db=# SELECT ARRAY(SELECT 1 UNION ALL SELECT 2);
?column?
----------
{1,2}
All good... now:
db=# SELECT ARRAY(SELECT array[1,2]::float[] UNION ALL SELECT
array[3,4]::float[]);
ERROR: could not find array type for data type double precision[]
db=# SELECT ARRAY(SELECT array[1,2]::integer[] UNION ALL SELECT
array[3,4]::integer[]);
ERROR: could not find array type for data type integer[]
Is that syntax supposed to work with anyarray types?
maxim.boguk@gmail.com writes:
SELECT ARRAY(SELECT ...)
doesn't work when subselect return any array.
Is that syntax supposed to work with anyarray types?
No.
regards, tom lane
On Mon, Dec 5, 2011 at 2:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
maxim.boguk@gmail.com writes:
SELECT ARRAY(SELECT ...)
doesn't work when subselect return any array.
Is that syntax supposed to work with anyarray types?No.
regards, tom lane
Hi.
Thank you very much for answer.
Ok the second problem (possible related to first becuse error is the same):
array_agg doesn't work with anyarray as well:
sports=# select array_agg(val) FROM (SELECT array[1,2]::integer[] as val
UNION ALL SELECT array[3,4]::integer[]) as t;
ERROR: could not find array type for data type integer[]
PS: I try create two-dimentional integer array from query results:
select val1::integer, val2::integer from somequery
to get integer[][] with content like:
[[val1-1,val1-2], [val2-1, val2-2], val[3-1, val3-2]... ]
Is there any way to perform that task it with reasonable efficiency?
--
Maxim Boguk
Senior Postgresql DBA.
Maxim Boguk <maxim.boguk@gmail.com> writes:
PS: I try create two-dimentional integer array from query results:
Well, you could do that with a suitably defined aggregate having the
signature "agg(anyarray) returns anyarray". But array_agg has the
signature "array_agg(anyelement) returns anyarray" so you can't use
it on an array input. 2-D arrays are not distinct from 1-D arrays
so far as the type system is concerned.
regards, tom lane