Updating a table which is null doesn't work?
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
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
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
Import Notes
Resolved by subject fallback