Re: Damn bug!

Bernie Huang wrote:
>
> I have a field using array (eg; col1 text[]) in Postgres, and it's a
> list of attributes. (eg; {"hi","hello","whatever","Empty",...})
>
> When I tried to update elements in the array via PHP script,
>
> $query = "update table
> set col1[1]='$var1',
> col1[2]='$var2',
> ...
> col1[4]='$var4'";
>
> it worked alright; however, when it came to the word 'Empty', it just
> wouldn't update. So, after a lot of struggle... )xp ... I finally
> replaced the word 'Empty' with 'None' or something alike, and it worked!
>

I think that there is something strange going on with assignment of
multiple array subscripts in PostgreSQL there...

It looks like a PostgreSQL limitation which is being detected on an
'insert' but is not being detected on an 'update' (and it probably
should be).

Look at the interesting log of various stuff from psql doing similar
things:

testing=# create table t1 ( c1 text[] );
CREATE
testing=# insert into t1 (c1[1], c1[2], c1[3] ) values('not',
'actually', 'empty' );
ERROR: Attribute 'c1' specified more than once
testing=# insert into t1 (c1[1], c1[2], c1[3] ) values('not',
'actually', 'empt' );
ERROR: Attribute 'c1' specified more than once
testing=# insert into t1 (c1 ) values('{"not", "actually", "empt"}' );
INSERT 373577 1
testing=# select * from t1;
c1
---------------------------
{"not","actually","empt"}
(1 row)

testing=# insert into t1 (c1 ) values('{"not", "actually", "empty"}' );
INSERT 373578 1
testing=# select * from t1;
c1
----------------------------
{"not","actually","empt"}
{"not","actually","empty"}
(2 rows)

testing=# update t1 set c1[2] = 'empty';
UPDATE 2
testing=# select * from t1;
c1
-------------------------
{"not","empty","empt"}
{"not","empty","empty"}
(2 rows)

testing=# update t1 set c1[2] = 'empty', c1[3] = 'full';
UPDATE 2
testing=# select * from t1;
c1
-------------------------
{"not","empty","full"}
{"not","empty","empty"}
(2 rows)

----------------------------------------------------
Now that was a bit strange, wasn't it?
----------------------------------------------------

testing=# update t1 set c1[2] = 'other', c1[3] = 'full';
UPDATE 2
testing=# select * from t1;
c1
-------------------------
{"not","other","full"}
{"not","other","empty"}
(2 rows)

testing=# update t1 set c1[3] = 'full';
UPDATE 2
testing=# select * from t1;
c1
------------------------
{"not","other","full"}
{"not","other","full"}
(2 rows)

testing=# update t1 set c1[2] = 'strange', c1[3] = 'notfull';
UPDATE 2
testing=# select * from t1;
c1
--------------------------
{"not","strange","full"}
{"not","strange","full"}
(2 rows)

----------------------------------------------------------
So it looks like UPDATE is silently ignoring second and subsequent
references to the same array variable. In most cases...

A good workaround muight be for you to use the '{"blah", "blah",
"blah"}' syntax for updating the array, although it's a pretty messy
syntax.

Cheers,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew(at)cat-it(dot)co(dot)nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267