Re: Empty arrays vs. NULLs, 9.1 & 8.3

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)
> 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