Surprising results from array concatenation

On Tue, Apr 25, 2017 at 9:26 AM, Mike Blackwell <mike.blackwell@rrd.com>
wrote:

The docs (section 9.18 for PG 9.6) show as an example for array
concatenation

ARRAY[4,5,6] || 7

which works fine. However, trying the same with an array of text doesn't
work:

# select array['a','b','c'] || 'd';
ERROR: malformed array literal: "d"
LINE 1: select array['a','b','c'] || 'd';
^
DETAIL: Array value must start with "{" or dimension information.

Casting the second value to TEXT works.

# select array['a','b','c'] || 'd'::TEXT;
?column?
-----------
{a,b,c,d}
(1 row)

The assumption that the second argument is an array constant seems
surprising

​It has to assume something. And for better and worse it has to assume it
without looking at the actual value.​ Choosing the scalar variant here
would be more convenient but choosing the same type as the left-hand side
is logical. Note that the concatenation operator/function isn't the one
complaining - if it was then intelligence could be inserted. The type
conversion code doesn't have the luxury.

I don't suppose one would get far arguing to modify the array input
function to convert a value that doesn't look like an array into a single
element text array. The "implicit conversion" that involves is something
we've gotten away from and seems like it would be worse that requiring the
explicit typing.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Tue, Apr 25, 2017 at 9:26 AM, Mike Blackwell <mike.blackwell@rrd.com>
wrote:

The docs (section 9.18 for PG 9.6) show as an example for array
concatenation
ARRAY[4,5,6] || 7
which works fine. However, trying the same with an array of text doesn't
work:
# select array['a','b','c'] || 'd';
ERROR: malformed array literal: "d"

The assumption that the second argument is an array constant seems
surprising

​It has to assume something. And for better and worse it has to assume it
without looking at the actual value.

Yeah. The core problem here is that the parser has to disambiguate the
|| operator: is it "anyarray || anyelement" or "anyarray || anyarray"?
In your first example the array can be seen to be int[] and 7 is taken
to be type int, so only "anyarray || anyelement" works. In the second
case it's looking at "int[] || unknown", and the relevant heuristic is
to assume that the "unknown" is the same type as the operator's other
input.

Peeking at the contents of the literal would make the behavior very
unpredictable/data-dependent, so we don't.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Apr 25, 2017 at 12:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Yeah. The core problem here is that the parser has to disambiguate the
|| operator: is it "anyarray || anyelement" or "anyarray || anyarray"?

​<...>​

Peeking at the contents of the literal would make the behavior very
unpredictable/data-dependent, so we don't.

​Fair enough.

Would a note in that section of the docs pointing out this behavior be
worthwhile?