Thread: Empty arrays vs. NULLs, 9.1 & 8.3

Empty arrays vs. NULLs, 9.1 & 8.3

From
Ken Tanzer
Date:
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!

Ken

p.s.,  On a side note, unless I've overlooked them before, the "this
page in other versions..." links in the doc pages seem to be new, and
are immensely helpful.  Especially because Google searches often
return results to the older versions.  Thanks a lot to whoever did
that!


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

Re: Empty arrays vs. NULLs, 9.1 & 8.3

From
John R Pierce
Date:
On 06/14/12 12:47 PM, Ken Tanzer wrote:
> p.s.,  On a side note, unless I've overlooked them before, the "this
> page in other versions..." links in the doc pages seem to be new, and
> are immensely helpful.  Especially because Google searches often
> return results to the older versions.

indeed, thats exactly why it was done.



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast


Re: Empty arrays vs. NULLs, 9.1 & 8.3

From
Steve Crawford
Date:
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


Re: Empty arrays vs. NULLs, 9.1 & 8.3

From
Ken Tanzer
Date:
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@pinpointresearch.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)
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
(253) 245-3801


Re: Empty arrays vs. NULLs, 9.1 & 8.3

From
Steve Crawford
Date:
On 06/14/2012 03:54 PM, Ken Tanzer wrote:
> Thanks Steve.  FWIW I looked at the 9.0 and 9.1 release notes, and
> didn't find much on arrays in them.
The notes are terse (a 1-2 line comment encapsulates the results of
hundreds of messages covering a couple year) but there are many
references to updates in how arrays are handled.
>
> 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.
I can't answer, specifically. But it makes a certain amount of sense. An
empty array has no elements so where does it "begin" or "end" -
especially since PostgreSQL array indexes don't need to start at 1 and
the array could be multi-dimensional. Unknown, aka null, seems the best
response.

Cheers,
Steve


Re: Empty arrays vs. NULLs, 9.1 & 8.3

From
Tom Lane
Date:
Ken Tanzer <ken.tanzer@gmail.com> writes:
> Thanks Steve.  FWIW I looked at the 9.0 and 9.1 release notes, and didn't
> find much on arrays in them.

This particular issue was fixed in 8.4.

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

Yeah, there are still a lot of inconsistencies there :-(.  Eventually
I'd like to see somebody go through all the array operations and make
a proposal for consistent handling of empty arrays.  I think it would
be better if we changed all those things at once, rather than causing
piecemeal compatibility hits.

            regards, tom lane