Re: Updating a table which is null doesn't work? (fwd)

> Correct.  If you'd like to propose and implement a different behavior,
> let's hear it.

I'm not expert enough to actually do the implementation, at least not yet,
(If I thought I had the skills in C, that's probably not the task I'd
tackle, I'd probably take on tablespaces.)

> I'm not necessarily averse to the idea, but we need a well-thought-out
> definition as to why a null array should combine with a non-null
> inserted element to yield a non-null result.  Most operations in SQL
> generate a null if either input is null.

Suppose my table has two rows:

 name | item
------+------
 ABC  |
 ABC  | {X}

Now consider the following SQL statement:

   update test_tbl set item[1] = 'Y' where name = 'ABC';

 name | item
------+------
 ABC  |
 ABC  | {Y}

Can you write a single update statement in SQL which will set item[1] in
both rows to 'Y' without having to know whether or not item is NULL?
(I'm not sure I could do it even KNOWING that one row has a null column,
and I don't think updates should be dependent on knowing whether or not
an array is null.)

If item is just char(1) and not an array, both rows are updated.  Thus
arrays are actually LESS consistent than independent columns.

I'm not sure how to extend this to slices, that'll take some thought.
--
Mike Nolan