Обсуждение: Are arrays broken in 7.0.3?

Can someone help me understay array usage in postgresql? After reading
the user manual (ch8), I tried to create a 2-d array of float8s...

and yet the database seems broken: when I ask for a row back I get
nothing!

Or, if I ask for a row another way, I get two rows! Please help!

Jason

p.s. how do you query the dimensionality of an n-dimensional array once it
is created?

postgresql 7.0.3 RPMs installed on Mandrake 7.1:
#rpm -qa | grep post
postgresql-python-7.0.3-2mdk
postgresql-devel-7.0.3-2mdk
postgresql-jdbc-7.0.3-2mdk
postgresql-7.0.3-2mdk
postgresql-tk-7.0.3-2mdk
postgresql-server-7.0.3-2mdk
postgresql-odbc-7.0.3-2mdk
postgresql-tcl-7.0.3-2mdk
postgresql-perl-7.0.3-2mdk
postgresql-test-7.0.3-2mdk

The following terminal session illustrates my concerns over arrays:

bash-2.04$ psql testdb
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

testdb=# create table arr (
testdb(# f float8[][]);
CREATE
testdb=# insert into arr values ('{{1, 2, 3, 4,
5},{6,7,8,9,10},{11,12,13,14,15},{16,17,18,19,20}}');
INSERT 18953 1
testdb=# select * from arr;
                                                  f
------------------------------------------------------------------------------------------------------

{{"1","2","3","4","5"},{"6","7","8","9","10"},{"11","12","13","14","15"},{"16","17","18","19","20"}}
(1 row)

testdb=# select f[1] from arr; (ask for a row back - get nothing!)
 f
---

(1 row)

testdb=# select f[1:2] from arr;
 f
---

(1 row)

testdb=# select f[2] from arr;
 f
---

(1 row)

testdb=# select f[2][1] from arr;
 f
---
 6
(1 row)

testdb=# select f[2][:] from arr;
ERROR:  parser: parse error at or near "]"
testdb=# select f[2][1:5] from arr; (ask row back, get two!)
                       f
------------------------------------------------
 {{"1","2","3","4","5"},{"6","7","8","9","10"}}
(1 row)

testdb=# select f[2][1:4] from arr;
                   f
---------------------------------------
 {{"1","2","3","4"},{"6","7","8","9"}}
(1 row)

Jason Aten <jaten@CS.UCLA.EDU> writes:
> testdb=# select f[2][1:5] from arr; (ask row back, get two!)

This is implicitly an array slice operation, so it's the same as
select f[1:2][1:5] from arr;
If you want just the one row you need to write
select f[2:2][1:5] from arr;

The behavior of the cases with too few subscripts does seem pretty
unhelpful --- seems like either raising an error or returning a
slice would make more sense than returning NULL.  But that's how
the code is set up at the moment.

            regards, tom lane

Tom:

Ah-hah! thanks!

Is there some way to find the dimensions of an array once it
has been inserted into a table? (or to just ask for it all, like saying
[:] in matlab? )

-Jason

On Mon, 18 Dec 2000, Tom Lane wrote:

> Jason Aten <jaten@CS.UCLA.EDU> writes:
> > testdb=# select f[2][1:5] from arr; (ask row back, get two!)
>
> This is implicitly an array slice operation, so it's the same as
> select f[1:2][1:5] from arr;
> If you want just the one row you need to write
> select f[2:2][1:5] from arr;
>
> The behavior of the cases with too few subscripts does seem pretty
> unhelpful --- seems like either raising an error or returning a
> slice would make more sense than returning NULL.  But that's how
> the code is set up at the moment.
>
>             regards, tom lane
>

Jason Aten <jaten@CS.UCLA.EDU> writes:
> Is there some way to find the dimensions of an array once it
> has been inserted into a table?

regression=# select array_dims(f) from arr;
 array_dims
------------
 [1:4][1:5]
(1 row)

The result is a text string, which isn't too helpful for a program;
might be nice to have some more program-friendly functions someday.

            regards, tom lane