Обсуждение: Array Madness

Hi.
I've started to fiddle with arrays. Every time I fiddle stuff breaks or
won't work for me :)

It seems that you can't have a NULL value in an array. I think based on
the idea of RDBMSes you should be able to have an array value that is
null... Running an update and setting a single array value to null seems
to nuke the entire array.

create table test(features int4[]);
insert into test values ('{NULL,1}');     
ERROR:  pg_atoi: error in "NULL": can't parse "NULL"
insert into test values ('{0,1}');
insert into test values ('{,1}');
select * from test;
features
--------
{0,1}   
{0,1}   
(2 rows)
update test set features[2]=NULL;
UPDATE 2
select * from test;              
features
--------              
(2 rows)

Positively a bad thing...

-Michael

> Hi.
> I've started to fiddle with arrays. Every time I fiddle stuff breaks or
> won't work for me :)
> 
> It seems that you can't have a NULL value in an array. I think based on
> the idea of RDBMSes you should be able to have an array value that is


Added to TODO:
* Allow nulls in arrays


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Michael Richards <miker@scifair.acadiau.ca> writes:
> It seems that you can't have a NULL value in an array.

Seems like a reasonable idea.  Go for it...

> Running an update and setting a single array value to null seems
> to nuke the entire array.

Yeah, it does, see ExecEvalArrayRef() in execQual.c: if the refassgnexpr
yields a null, it just drops out with a null result for the whole expr.
This could be fixed if the array routines had a representation for a
null element in an array, but they don't.

In the meantime, perhaps it would be better for ExecEvalArrayRef() to
raise an error if the assign source is null, rather than dropping the
whole contents of the array.  Comments?
        regards, tom lane

On Tue, 14 Sep 1999, Tom Lane wrote:

> In the meantime, perhaps it would be better for ExecEvalArrayRef() to
> raise an error if the assign source is null, rather than dropping the
> whole contents of the array.  Comments?

This sounds like a hack that avoids the real issue... Bruce has added it
to the todo list. I'm going to re-write the code for ALTER TABLE,
otherwise I'd have time to play with this one. I guess for now I will
design my implementation so a value of '0' is not used as a key.

-Michael

Michael Richards <miker@scifair.acadiau.ca> writes:
> On Tue, 14 Sep 1999, Tom Lane wrote:
>> In the meantime, perhaps it would be better for ExecEvalArrayRef() to
>> raise an error if the assign source is null, rather than dropping the
>> whole contents of the array.  Comments?

> This sounds like a hack that avoids the real issue...

Well, of course it's not a solution to the real issue, but it seemed
like it might prevent someone from accidentally losing data until
we get around to providing a real solution.  (Which could be a long
time; there's a lot of things on the TODO list...)
        regards, tom lane