Re: Empty arrays vs. NULLs, 9.1 & 8.3

Thanks Steve. FWIW I looked at the 9.0 and 9.1 release notes, and didn't
find much on arrays in them.

I do have one follow-up curiosity question, though. Why does
array_dims(array[]::varchar[]) return NULL instead of 0? I would expect
NULL for a NULL array, but not an empty one. (And the same for
array_[upper,lower,length] functions as well.

There doesn't seem to be much coverage of NULLs in the array documentation,
so in the making-work-for-other-people department, I'd suggest that either
weaving it in or including a small separate section on the topic might be
helpful.

Cheers,
Ken

On Thu, Jun 14, 2012 at 3:15 PM, Steve Crawford <
scrawford(at)pinpointresearch(dot)com> wrote:

> On 06/14/2012 12:47 PM, Ken Tanzer wrote:
>
>> Hi. I had this piece of SQL, which ran fine on my 9.1 installation:
>>
>> INSERT INTO foo
>> SELECT ...,
>> CASE WHEN NOT allow_output_screen THEN array['O_SCREEN'] ELSE
>> array[]::varchar[] END
>> || CASE WHEN NOT allow_output_spreadsheet THEN array['O_TEMPLATE']
>> ELSE array[]::varchar[] END,
>> ...;
>>
>> However, this failed miserably on someone else's 8.3:
>>
>> ERROR: syntax error at or near "]"
>> LINE 1: ...w_output_screen THEN array['O_SCREEN'] ELSE array[]::varchar
>>
>> ^
>> The 9.1 documentation
>> (http://www.postgresql.org/**docs/9.1/static/sql-**expressions.html<http://www.postgresql.org/docs/9.1/static/sql-expressions.html>
>> )
>> states you can construct an empty array with my syntax (that's how I
>> got it originally), but there is no mention of empty arrays in the
>> corresponding 8.3 page.
>>
>> In 8.3, I can SELECT NULL::varchar[], which seems to behave the same
>> in my query. The two don't seem to be exactly the same. I'm a little
>> confused, however, as to the finer points or conceptual differences
>> between them, and also what the differences might be between 8.3 and
>> 9.1.
>>
>> Sticking within 9.1, I ran this:
>>
>> =>CREATE TEMPORARY TABLE array_fun( f1 varchar[] );
>> =>INSERT INTO array_fun VALUES (array[]::varchar[]),(NULL::**varchar[]);
>>
>> CREATE TABLE
>> INSERT 0 2
>>
>> => SELECT array_dims(f1) AS dims, f1 IS NULL as is_null, f1 ||
>> array['Item 2']::varchar[] AS concats,f1 FROM array_fun;
>>
>> dims | is_null | concats | f1
>> ------+---------+------------+**----
>> | f | {"Item 2"} | {}
>> | t | {"Item 2"} |
>> (2 rows)
>>
>> If anyone can shed some light on this, and also how to construct an
>> empty array in 8.3, it would be great. Thanks!
>>
>>
> Array handling in general has undergone many changes from 8.3 to 9.1 and
> more if you go back from 8.3. Check the release notes for each major
> release for the specifics and logic. Some of the changes such as how to
> handle string_to_array('') (my fault for bringing it up originally)
> required discussions that spanned a couple major versions.
>
> To create an empty array in 8.3 you can just use '{}' and note that an
> empty array is *not* null while NULL::varchar[] *is*.
>
> Cheers,
> Steve
>
>

--
AGENCY Software
A data system that puts you in control
*http://agency-software.org/*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801