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