Updating a table which is null doesn't work?

Поиск
Список
Период
Сортировка
Искать

Updating a table which is null doesn't work?

От:
nolan@celery.tssi.com
Дата:
Create a table as follows:

    create table test_tbl (name varchar(3), item char(1)[])

Insert a row:
   
    insert into test_tbl values ('ABC',null);

Update a value in the array:

    update test_tbl set item[1] = 'Z';

Now do a select:

    select * from test_tbl;

What happened to the update?

It appears to be necessary to set the array to some non-null value
before it is possible to update any element in it.  This would appear 
to be inconsistent with what happens when you update a non-arrayed column
that currently contains a null.
--
Mike Nolan

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

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
nolan@celery.tssi.com writes:
> It appears to be necessary to set the array to some non-null value
> before it is possible to update any element in it.

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

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.

			regards, tom lane
FAQ