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 < scrawf...@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<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.tan...@agency-software.org (253) 245-3801
