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:
Show quoted text
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