"Ian Caulfield" <ian.caulfield@gmail.com> writes:
> I think array_agg also keeps nulls - although the draft standard I
> have seems to contradict itself about this...
The SQL:2008 draft I have says, in 10.9 <aggregate function> general
rule 8g
NOTE 267 - Null values are not eliminated when computing <arrayaggregate function>. This, plus the optional <sort
specificationlist>,sets <array aggregate function> apart from <general setfunction>s.
So that seems to make it perfectly clear that nulls aren't eliminated,
and furthermore to be an intentional override of any other part of the
spec that you might think says nulls should be eliminated. If you have
an argument to read it otherwise, please say exactly what.
A larger objection to Jeff's draft patch is that it doesn't implement
the <sort specification list>. I'm entirely happy about not doing that
--- the current SQL committee's willingness to invent random new syntax
and nonorthogonal behavior for every function they can think of will be
the death of SQL yet --- but it's something that we at least need to
document the workaround for.
regards, tom lane