Re: Arrays and "goodness" in RDBMSs (was Re: join of array)

Hello,

Is possible merge two arrays like

array[1,2,3] + array[4,5,6] => array[1,2,3,4,5,6]

select array_append(array[1,2,3], array[2,3]);
ERROR: function array_append(integer[], integer[]) does not exist

regards
Pavel Stehule

Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz> writes:
> Is possible merge two arrays like
> array[1,2,3] + array[4,5,6] => array[1,2,3,4,5,6]

I was about to say that || would do it, but I see that's not quite
right:

regression=# SELECT ARRAY[1,2,3] || ARRAY[4,5,6];
?column?
-------------------
{{1,2,3},{4,5,6}}
(1 row)

Offhand, I would think that '{1,2,3,4,5,6}' would be what I'd
intuitively expect to get from "concatenating" these arrays.
Joe, do we really have this implemented per spec?

regards, tom lane

Tom Lane wrote:
> Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz> writes:
>
>>Is possible merge two arrays like
>>array[1,2,3] + array[4,5,6] => array[1,2,3,4,5,6]
>
>
> I was about to say that || would do it, but I see that's not quite
> right:
>
> regression=# SELECT ARRAY[1,2,3] || ARRAY[4,5,6];
> ?column?
> -------------------
> {{1,2,3},{4,5,6}}
> (1 row)
>
> Offhand, I would think that '{1,2,3,4,5,6}' would be what I'd
> intuitively expect to get from "concatenating" these arrays.
> Joe, do we really have this implemented per spec?
>

Hmmm, it made sense to me, at at least at some point ;-). Here's the
SQL99 guidance (SQL200X doesn't give any more detailed guidance):

4.11.3.2 Operators that operate on array values and return array values
<array concatenation> is an operation that returns the array value made
by joining its array value operands in the order given.

So I guess it ought to be changed.

We also have
ARRAY[1,2] || 3 == '{1,2,3}'
and
ARRAY[[1,2],[3,4]] || ARRAY[5,6] == '{{1,2},{3,4},{5,6}}'
and
ARRAY[[1,2],[3,4]] || ARRAY[[1,2],[3,4]] ==
'{{{1,2},{3,4}},{{1,2},{3,4}}}'

I think the first two still make sense. I guess the third case ought to be:
ARRAY[[1,2],[3,4]] || ARRAY[[1,2],[3,4]] ==
'{{1,2},{3,4},{1,2},{3,4}}'
?

If this sounds good, I'll work on a patch for the behavior as well as
the docs.

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> Hmmm, it made sense to me, at at least at some point ;-). Here's the
> SQL99 guidance (SQL200X doesn't give any more detailed guidance):

> 4.11.3.2 Operators that operate on array values and return array values
> <array concatenation> is an operation that returns the array value made
> by joining its array value operands in the order given.

That's about as clear as mud :-( ... but I found a clearer statement in
SQL99 6.31:

2) If <array concatenation> is specified, then:

a) Let AV1 be the value of <array value expression 1> and let
AV2 be the value of <array value expression 2>.

b) If either AV1 or AV2 is the null value, then the result of
the <array concatenate function> is the null value.

c) Otherwise, the result is the array comprising every element
of AV1 followed by every element of AV2.

(c) seems to be pretty clearly what Pavel wants for the 1-D case, but
it's not immediately clear how to apply it to multidimensional arrays.

> We also have
> ARRAY[1,2] || 3 == '{1,2,3}'
> and
> ARRAY[[1,2],[3,4]] || ARRAY[5,6] == '{{1,2},{3,4},{5,6}}'
> and
> ARRAY[[1,2],[3,4]] || ARRAY[[1,2],[3,4]] ==
> '{{{1,2},{3,4}},{{1,2},{3,4}}}'

> I think the first two still make sense. I guess the third case ought to be:
> ARRAY[[1,2],[3,4]] || ARRAY[[1,2],[3,4]] ==
> '{{1,2},{3,4},{1,2},{3,4}}'
> ?

Probably. AFAICS this doesn't affect the data copying at all, only the
way in which the result's dimension values are computed, right?

Also, we might want to take another look at the rules for selecting the
lower-bounds of the result array. In the cases where we're joining
N+1-D to N-D (including 1-D to scalar) it still seems to make sense to
preserve the subscripts of the higher-dimensional object, so the lower-
dimensional one is "pushed" onto one end or the other. In the N-D to
N-D case I can't see any really principled way to do it; for lack of
a better idea, I suggest preserving the subscripts of the lefthand
input (ie, using its lower-bound).

regards, tom lane

>
> ARRAY[[1,2],[3,4]] || ARRAY[[1,2],[3,4]] ==
> '{{{1,2},{3,4}},{{1,2},{3,4}}}'
>
> I think the first two still make sense. I guess the third case ought to be:
> ARRAY[[1,2],[3,4]] || ARRAY[[1,2],[3,4]] ==
> '{{1,2},{3,4},{1,2},{3,4}}'
> ?

I do not think this is right. I think the current behaviour
is right. You are effectively dereferencing or flattening
the second array which changes the definition of the second
object.

The ability to do the dereference/flattening is useful,
but it is not the || operator. How about |* which would
flatten 1 level? Of course, that begs the question
of what about n levels and I'm not sure about that.

--elein

On Fri, Aug 15, 2003 at 08:34:14AM -0700, Joe Conway wrote:
> Tom Lane wrote:
> >Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz> writes:
> >
> >>Is possible merge two arrays like
> >>array[1,2,3] + array[4,5,6] => array[1,2,3,4,5,6]
> >
> >
> >I was about to say that || would do it, but I see that's not quite
> >right:
> >
> >regression=# SELECT ARRAY[1,2,3] || ARRAY[4,5,6];
> > ?column?
> >-------------------
> > {{1,2,3},{4,5,6}}
> >(1 row)
> >
> >Offhand, I would think that '{1,2,3,4,5,6}' would be what I'd
> >intuitively expect to get from "concatenating" these arrays.
> >Joe, do we really have this implemented per spec?
> >
>
> Hmmm, it made sense to me, at at least at some point ;-). Here's the
> SQL99 guidance (SQL200X doesn't give any more detailed guidance):
>
> 4.11.3.2 Operators that operate on array values and return array values
> <array concatenation> is an operation that returns the array value made
> by joining its array value operands in the order given.
>
> So I guess it ought to be changed.
>
> We also have
> ARRAY[1,2] || 3 == '{1,2,3}'
> and
> ARRAY[[1,2],[3,4]] || ARRAY[5,6] == '{{1,2},{3,4},{5,6}}'
> and
> ARRAY[[1,2],[3,4]] || ARRAY[[1,2],[3,4]] ==
> '{{{1,2},{3,4}},{{1,2},{3,4}}}'
>
> I think the first two still make sense. I guess the third case ought to be:
> ARRAY[[1,2],[3,4]] || ARRAY[[1,2],[3,4]] ==
> '{{1,2},{3,4},{1,2},{3,4}}'
> ?
>
> If this sounds good, I'll work on a patch for the behavior as well as
> the docs.
>
> Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

elein wrote:
>> ARRAY[[1,2],[3,4]] || ARRAY[[1,2],[3,4]] ==
>> '{{{1,2},{3,4}},{{1,2},{3,4}}}'
>>
>>I think the first two still make sense. I guess the third case ought to be:
>> ARRAY[[1,2],[3,4]] || ARRAY[[1,2],[3,4]] ==
>> '{{1,2},{3,4},{1,2},{3,4}}'
>>?
>
> I do not think this is right. I think the current behaviour
> is right. You are effectively dereferencing or flattening
> the second array which changes the definition of the second
> object.

It makes sense in analogy to
ARRAY[1,2] || ARRAY[3,4] == '{1,2,3,4}'

In the case of, e.g. ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8]],
'{1,2}', '{3,4}', '{5,6}', and '{7,8}' are "elements" of the higher
level array, just like 1, 2, 3, & 4 are elements of '{1,2,3,4}'

Joe

I'd like to summarize what I know (or don't know) since this topic has been
hit around a little and I'm new to this. I'm hoping it will clear things up,
at least for me. You are all the experts, I want to make sure I am singing
from the same page.

data sample:
id | fm | ls | addr | city | st | z |c| start|end
----------------------------------------------------------------------------------

191922C,Bob Cobb,D'Obbalina Sr.,312 Elm Street,Yountville,CA,94599,5,062001,082009
339111C,Elma Thelma,Velma,98 Oak Lane,St. Louis,MO,63119-2065,,,

What I wanted to do was to import lots of these from a text file. In the case
where there is an empty string (i.e. no value after a comma) I wanted to
define the column in the table in a way that would accept the empty string but
replace it with the default value for that column. I didn't know that the
copy command is just some C code that stuffs the data into the db ala
fois grois.

What I would really benefit from (and I hope some other new soul would too)
is if someone would outline exactly how they would approach this problem.

Maybe provide the correct table definition and the copy command. Or if that
just won't work an alternate approach. I realize that some of you have
done this partially but there have been too many replies to get into a
single cohesive instruction.

Anyway I suppose my initial frustration in trying to do this may have blinded
me from reason.

create table contact (
id character(7) NOT NULL,
fm character(30) DEFAULT 'xzxzxzxz',
ls character(30) DEFAULT 'xzxzxzxz',
addr character(30) DEFAULT '123 xzxzxzxz',
city character(25) DEFAULT 'xzxzxzxz',
st character(2) DEFAULT 'xz',
c character(1) DEFAULT 'x',
start decimal(6) DEFAULT 122038,
end decimal(6) DEFAULT 122038,
CONSTRAINT handle PRIMARY KEY (id)
) WITHOUT OIDS;

you said we had:

> We also have
> ARRAY[1,2] || 3 == '{1,2,3}'
> and
> ARRAY[[1,2],[3,4]] || ARRAY[5,6] == '{{1,2},{3,4},{5,6}}'
> and
> ARRAY[[1,2],[3,4]] || ARRAY[[1,2],[3,4]] ==
> '{{{1,2},{3,4}},{{1,2},{3,4}}}'
>
> I think the first two still make sense. I guess the third case ought to be:
> ARRAY[[1,2],[3,4]] || ARRAY[[1,2],[3,4]] ==
> '{{1,2},{3,4},{1,2},{3,4}}'

The second case above makes this case wrong. Or vice versa.
It is dereferencing the right operand in one case and
not in the other. How exactly do you tell the difference?
The only difference I can tell is the depth of the
left hand array. This is confusing and unnecessarily complex.

> It makes sense in analogy to
> ARRAY[1,2] || ARRAY[3,4] == '{1,2,3,4}'
This case should be '{1,2,{3,4}}'.

Unless we have a flattening operator or dereferencing function.
Or a policy of always flattening the right operand one
level (confusing!).

Consistent treatment of the operands is important. Treating
object (arrays) as they were defined makes the nesting
and usage easier. Not to mention the implementation.

(Good problem :-)

Elein

On Fri, Aug 15, 2003 at 10:06:36AM -0700, Joe Conway wrote:
> elein wrote:
> >> ARRAY[[1,2],[3,4]] || ARRAY[[1,2],[3,4]] ==
> >> '{{{1,2},{3,4}},{{1,2},{3,4}}}'
> >>
> >>I think the first two still make sense. I guess the third case ought to
> >>be:
> >> ARRAY[[1,2],[3,4]] || ARRAY[[1,2],[3,4]] ==
> >> '{{1,2},{3,4},{1,2},{3,4}}'
> >>?
> >
> >I do not think this is right. I think the current behaviour
> >is right. You are effectively dereferencing or flattening
> >the second array which changes the definition of the second
> >object.
>
> It makes sense in analogy to
> ARRAY[1,2] || ARRAY[3,4] == '{1,2,3,4}'
>
> In the case of, e.g. ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8]],
> '{1,2}', '{3,4}', '{5,6}', and '{7,8}' are "elements" of the higher
> level array, just like 1, 2, 3, & 4 are elements of '{1,2,3,4}'
>
> Joe
>

elein wrote:
> you said we had:
>
>>We also have
^^^^

There are two variants each of two cases. The first case is what started
this discussion. The newest reading of the SQL99 spec says that we
*must* do this:
1a) ARRAY[1,2] || ARRAY[3,4] == '{1,2,3,4}'

Quoting the paragraph provided by Tom:
"c) Otherwise, the result is the array comprising every element
of AV1 followed by every element of AV2."

The variant is that when you have an "array of arrays", i.e. a
multidimensional array (which Peter E pointed out earlier is part of
SQL99 too), the spec wording implies that we also *must* do this:
1b) ARRAY[[1],[2]] || ARRAY[[3],[4]] == '{{1},{2},{3},{4}'

The second case is not directly addressed by the spec as far as I can
see, i.e. it is a Postgres extension. That is:
2a) ARRAY[1,2] || 3 == '{1,2,3}'

So by analogy the multidimensional variant is:
2b) ARRAY[[1],[2]] || ARRAY[3] == '{{1},{2},{3}}'

Cases 1a and 1b are currently wrong according to the spec., and that's
the change we've been discussing. Cases 2a and 2b currently work as
shown and are correct IMHO (although Tom pointed out a lower bound index
issue that I'll address in my response to him).

Does this help?

Joe

Tom Lane wrote:
> That's about as clear as mud :-( ... but I found a clearer statement
> in SQL99 6.31:
>
> 2) If <array concatenation> is specified, then:
>
> a) Let AV1 be the value of <array value expression 1> and let AV2 be
> the value of <array value expression 2>.
>
> b) If either AV1 or AV2 is the null value, then the result of the
> <array concatenate function> is the null value.
>
> c) Otherwise, the result is the array comprising every element of AV1
> followed by every element of AV2.
>
> (c) seems to be pretty clearly what Pavel wants for the 1-D case, but
> it's not immediately clear how to apply it to multidimensional
> arrays.
>

Thanks -- I found the corresponding paragraph in SQL200x (6.35) and it
pretty much reads the same.

> Probably. AFAICS this doesn't affect the data copying at all, only
> the way in which the result's dimension values are computed, right?

Looks that way to me.

> Also, we might want to take another look at the rules for selecting
> the lower-bounds of the result array. In the cases where we're
> joining N+1-D to N-D (including 1-D to scalar) it still seems to make
> sense to preserve the subscripts of the higher-dimensional object, so
> the lower- dimensional one is "pushed" onto one end or the other.

This is mostly the way it currently works:

regression=# create table arr(f1 int[]);
CREATE TABLE
regression=# insert into arr values ('{}');
INSERT 2498103 1
regression=# update arr set f1[-2] = 1;
UPDATE 1
regression=# select array_lower(f1,1) from arr;
array_lower
-------------
-2
(1 row)

regression=# select array_lower(f1 || 2, 1) from arr;
array_lower
-------------
-2
(1 row)

regression=# select array_lower(0 || f1, 1) from arr;
array_lower
-------------
-3
(1 row)
regression=# update arr set f1 = ARRAY[[1,2],[3,4]];
UPDATE 1
regression=# select array_lower(f1,1) from arr;
array_lower
-------------
1
(1 row)

regression=# select array_lower(f1 || ARRAY[5,6], 1) from arr;
array_lower
-------------
1
(1 row)

regression=# select array_lower(ARRAY[-1,0] || f1, 1) from arr;
array_lower
-------------
1
(1 row)

It looks like the only "wrong" case is the last one. Will fix.

> In the N-D to N-D case I can't see any really principled way to do
> it; for lack of a better idea, I suggest preserving the subscripts of
> the lefthand input (ie, using its lower-bound).

OK, will do.

Thanks,

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> elein wrote:
>> I do not think this is right. I think the current behaviour
>> is right. You are effectively dereferencing or flattening
>> the second array which changes the definition of the second
>> object.

> It makes sense in analogy to
> ARRAY[1,2] || ARRAY[3,4] == '{1,2,3,4}'

I agree with Joe. The spec is quite clear about what to do in the
one-dimensional case: the original arrays lose their separate identity.
In the multi-dimensional case, they should lose their separate
identities in the outermost dimension.

I believe the behavior Elein wants can be had by writing
ARRAY[ n_d_array , n_d_array ]
(Joe, would you confirm that's true, and document it? I don't think
either section 8.10 or section 4.2.8 makes clear that you can build
arrays from smaller array values rather than just scalars.) As long as
we have that alternative, it's not necessary that concatenation do the
same thing.

Another argument for doing it this way is that it makes array
concatenation associative, which is a nice bit of symmetry. Consider

ARRAY[[1,1],[2,2]] || ARRAY[[3,3],[4,4]] ||
ARRAY[[5,5],[6,6]] || ARRAY[[7,7],[8,8]]

Right now, with the default left-to-right association you get

{{{1,1},{2,2}},{{3,3},{4,4}},{{5,5},{6,6}},{{7,7},{8,8}}}

but if you parenthesize it differently you can get a different answer:

regression=# select (ARRAY[[1,1],[2,2]] || ARRAY[[3,3],[4,4]]) || (ARRAY[[5,5],[6,6]] || ARRAY[[7,7],[8,8]]);
?column?
---------------------------------------------------------------
{{{{1,1},{2,2}},{{3,3},{4,4}}},{{{5,5},{6,6}},{{7,7},{8,8}}}}
(1 row)

With the flattening approach all the intermediate results will remain
2-D arrays and so you get the same answer for all parenthesizations,
namely {{1,1},{2,2},{3,3},{4,4},{5,5},{6,6},{7,7},{8,8}}. That strikes
me as more nearly the intuitive meaning of "concatenation" than what
we've got now.

(Cases involving some N-D and some N+1-D inputs remain non-associative,
though, which is a tad annoying. Maybe it's okay seeing that the inputs
are of different kinds.)

regards, tom lane

Tom Lane wrote:
> I believe the behavior Elein wants can be had by writing
> ARRAY[ n_d_array , n_d_array ]
> (Joe, would you confirm that's true, and document it? I don't think
> either section 8.10 or section 4.2.8 makes clear that you can build
> arrays from smaller array values rather than just scalars.) As long as
> we have that alternative, it's not necessary that concatenation do the
> same thing.

Well this works:
regression=# select ARRAY[ARRAY[[1,2],[3,4]],ARRAY[[5,6],[7,8]]];
array
-------------------------------
{{{1,2},{3,4}},{{5,6},{7,8}}}
(1 row)

But I was disappointed that this doesn't:

regression=# select ARRAY['{{1,2},{3,4}}'::int[],'{{5,6},{7,8}}'::int[]];
ERROR: multidimensional ARRAY[] must be built from nested array expressions

Nor does this:

create table arr(f1 int[], f2 int[]);
insert into arr values (ARRAY[[1,2],[3,4]],ARRAY[[5,6],[7,8]]);
regression=# select ARRAY[f1,f2] from arr;
ERROR: multidimensional ARRAY[] must be built from nested array expressions

It does work for the element to array case:

create table els(f1 int, f2 int);
insert into els values (1,2);
regression=# select ARRAY[f1,f2] from els;
array
-------
{1,2}
(1 row)

Should I try to make the second and third cases work?

Joe

On Fri, 2003-08-15 at 12:41, Joe Conway wrote:
> Tom Lane wrote:
> > That's about as clear as mud :-( ... but I found a clearer statement
> > in SQL99 6.31:
> >
> > 2) If <array concatenation> is specified, then:
> >
> > a) Let AV1 be the value of <array value expression 1> and let AV2 be
> > the value of <array value expression 2>.
> >
> > b) If either AV1 or AV2 is the null value, then the result of the
> > <array concatenate function> is the null value.
> >
> > c) Otherwise, the result is the array comprising every element of AV1
> > followed by every element of AV2.
> >
> > (c) seems to be pretty clearly what Pavel wants for the 1-D case, but
> > it's not immediately clear how to apply it to multidimensional
> > arrays.
> >
>
> Thanks -- I found the corresponding paragraph in SQL200x (6.35) and it
> pretty much reads the same.

Why are arrays even mentioned in the the same breath wrt relations
DBMSs? Aren't they an anathema to all we know and love?

--
+---------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron(dot)l(dot)johnson(at)cox(dot)net |
| Jefferson, LA USA |
| |
| "Man, I'm pretty. Hoo Hah!" |
| Johnny Bravo |
+---------------------------------------------------------------+

I guess I am arguing against the spec. :-)
But given the spec...
The spec is consistent in that it seems to
dereference the right operand one level.

However, that would still make 2b inconsistent
in the evaluation of the right operand.

> 1a) ARRAY[1,2] || ARRAY[3,4] == '{1,2,3,4}'
> 1b) ARRAY[[1],[2]] || ARRAY[[3],[4]] == '{{1},{2},{3},{4}}'
and ARRAY[1,2] || ARRAY[[3],[4]] == '{1,2,{3},{4}}'

> So by analogy the multidimensional variant is:
> 2b) ARRAY[[1],[2]] || ARRAY[3] == '{{1},{2},{3}}'
I would think this would be '{{1},{2}, 3}}'
and ARRAY[1,2] || ARRAY[[3],[4]] == '{1,2,{3},{4}}'

I do see the analogy you are making. But I
respectfully disagree (with the spec ?) that
the type/structure of the left operand should be
taken into account when evaluating the right operand.

elein

On Fri, Aug 15, 2003 at 10:36:54AM -0700, Joe Conway wrote:
> elein wrote:
> >you said we had:
> >
> >>We also have
> ^^^^
>
> There are two variants each of two cases. The first case is what started
> this discussion. The newest reading of the SQL99 spec says that we
> *must* do this:
> 1a) ARRAY[1,2] || ARRAY[3,4] == '{1,2,3,4}'
>
> Quoting the paragraph provided by Tom:
> "c) Otherwise, the result is the array comprising every element
> of AV1 followed by every element of AV2."
>
> The variant is that when you have an "array of arrays", i.e. a
> multidimensional array (which Peter E pointed out earlier is part of
> SQL99 too), the spec wording implies that we also *must* do this:
> 1b) ARRAY[[1],[2]] || ARRAY[[3],[4]] == '{{1},{2},{3},{4}'
>
>
> The second case is not directly addressed by the spec as far as I can
> see, i.e. it is a Postgres extension. That is:
> 2a) ARRAY[1,2] || 3 == '{1,2,3}'
>
> So by analogy the multidimensional variant is:
> 2b) ARRAY[[1],[2]] || ARRAY[3] == '{{1},{2},{3}}'
>
> Cases 1a and 1b are currently wrong according to the spec., and that's
> the change we've been discussing. Cases 2a and 2b currently work as
> shown and are correct IMHO (although Tom pointed out a lower bound index
> issue that I'll address in my response to him).
>
> Does this help?
>
> Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

Joe Conway <mail(at)joeconway(dot)com> writes:
> But I was disappointed that this doesn't:

> regression=# select ARRAY['{{1,2},{3,4}}'::int[],'{{5,6},{7,8}}'::int[]];
> ERROR: multidimensional ARRAY[] must be built from nested array expressions

Drat, I was assuming that that *would* work.

> Should I try to make the second and third cases work?

Could you look at how big a change it'd be, anyway? Offhand I think it
may just mean that the subscript-checking done in parse_expr.c needs to
be done at runtime instead. Remember parse_expr should only be
concerned about determining datatype, and for its purposes all arrays of
a given element type are the same --- subscript checking should happen
at runtime. (It seems likely that having an ndims field in ArrayExpr
is inappropriate.)

regards, tom lane

PostgreSQL is an ORDBMS, not just an RDBMS.

A column holds a type of value. Any kind. The
structure and operands define the type. The data
defines the value. This holds true for simple types
like an integer or complex types like an array.

The database data is relatively "type blind" in an
ORDBMS. It uses the standard overloaded operands
to determine the type of function to perform for
all of the usual RDBMS utilities.
Constraints, triggers, sorting, etc. all apply.

That's what the ORDBMS stuff can give you.
Arrays are a natural extension.

Arrays don't necessarily imply denormalization.
It depends on how you use them. The same rule
applies for integers.

elein

On Fri, Aug 15, 2003 at 01:13:52PM -0500, Ron Johnson wrote:
>
> Why are arrays even mentioned in the the same breath wrt relations
> DBMSs? Aren't they an anathema to all we know and love?
>
> --
> +---------------------------------------------------------------+
> | Ron Johnson, Jr. Home: ron(dot)l(dot)johnson(at)cox(dot)net |
> | Jefferson, LA USA |
> | |
> | "Man, I'm pretty. Hoo Hah!" |
> | Johnny Bravo |
> +---------------------------------------------------------------+
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

On Friday 15 August 2003 01:13 pm, Ron Johnson wrote:
> On Fri, 2003-08-15 at 12:41, Joe Conway wrote:
> > Tom Lane wrote:
> > > That's about as clear as mud :-( ... but I found a clearer statement
> > > in SQL99 6.31:
> > >
> > > 2) If <array concatenation> is specified, then:
> > >
> > > a) Let AV1 be the value of <array value expression 1> and let AV2 be
> > > the value of <array value expression 2>.
> > >
> > > b) If either AV1 or AV2 is the null value, then the result of the
> > > <array concatenate function> is the null value.
> > >
> > > c) Otherwise, the result is the array comprising every element of AV1
> > > followed by every element of AV2.
> > >
> > > (c) seems to be pretty clearly what Pavel wants for the 1-D case, but
> > > it's not immediately clear how to apply it to multidimensional
> > > arrays.
> >
> > Thanks -- I found the corresponding paragraph in SQL200x (6.35) and it
> > pretty much reads the same.
>
> Why are arrays even mentioned in the the same breath wrt relations
> DBMSs? Aren't they an anathema to all we know and love?

This gives rise to a couple of good questions:

When and why would you want to use arrays instead of a relational model?

Thanks,

Andrew Gould

Hi,

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>
>>But I was disappointed that this doesn't:
>
>
>>regression=# select ARRAY['{{1,2},{3,4}}'::int[],'{{5,6},{7,8}}'::int[]];
>>ERROR: multidimensional ARRAY[] must be built from nested array expressions
>
>
> Drat, I was assuming that that *would* work.
>
>
>>Should I try to make the second and third cases work?
>
>
> Could you look at how big a change it'd be, anyway? Offhand I think it
> may just mean that the subscript-checking done in parse_expr.c needs to
> be done at runtime instead. Remember parse_expr should only be
> concerned about determining datatype, and for its purposes all arrays of
> a given element type are the same --- subscript checking should happen
> at runtime. (It seems likely that having an ndims field in ArrayExpr
> is inappropriate.)

Wouldn't it be a good idea to just extend the partner arrays? Say
if we concenate array A(Na,..,Xa) || B(Nb,...,Xb)
The resulting array C would be of dimension
C(Na+Nb,max(Oa,Ob),max(Pa,Pb), ... max(Xa,Xb))
So concenation would be an extending and right hand appending (at first
level)

Regards
Tino Wildenhain

On Fri, 2003-08-15 at 13:32, elein wrote:
> PostgreSQL is an ORDBMS, not just an RDBMS.

But y'all are talking about the SQL standard here.

> A column holds a type of value. Any kind. The
> structure and operands define the type. The data
> defines the value. This holds true for simple types
> like an integer or complex types like an array.
>
> The database data is relatively "type blind" in an
> ORDBMS. It uses the standard overloaded operands
> to determine the type of function to perform for
> all of the usual RDBMS utilities.
> Constraints, triggers, sorting, etc. all apply.
>
> That's what the ORDBMS stuff can give you.
> Arrays are a natural extension.
>
> Arrays don't necessarily imply denormalization.
> It depends on how you use them. The same rule
> applies for integers.

I dunno 'bout that...

> elein
>
> On Fri, Aug 15, 2003 at 01:13:52PM -0500, Ron Johnson wrote:
> >
> > Why are arrays even mentioned in the the same breath wrt relations
> > DBMSs? Aren't they an anathema to all we know and love?

--
+---------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron(dot)l(dot)johnson(at)cox(dot)net |
| Jefferson, LA USA |
| |
| "Man, I'm pretty. Hoo Hah!" |
| Johnny Bravo |
+---------------------------------------------------------------+

In response to both Andrew Gould and Ron Johnson...

If arrays are not natural in the organization of
your data, don't use them. That is the guideline.

If the array defines something specific they are
very natural. The confusion could be that arrays
are abstract types.

Specific implementations which use arrays might
be clearer. For example, a definition of a polygon
is an array of Points. Points, themselves are an
array.

(The actual postgreSQL implementation of polygons and points
doesn't use the newer cleaner array abstraction, I think.
But if I were reimplementing them, I would build on
top of the new array capabilities. The point is to show
an array structured object which makes sense in context.)

Of course you can denomalize via arrays, but it tends
to make things harder for you. And I believe the
same thing is true for denormalized integer columns.

elein
=============================================================
elein(at)varlena(dot)com www.varlena.com
PostgreSQL Consulting & Support
PostgreSQL General Bits http://www.varlena.com/GeneralBits/
=============================================================
"Free your mind the rest will follow"
-- En Vogue

On Fri, Aug 15, 2003 at 02:20:18PM -0500, Ron Johnson wrote:
> On Fri, 2003-08-15 at 13:32, elein wrote:
> > PostgreSQL is an ORDBMS, not just an RDBMS.
>
> But y'all are talking about the SQL standard here.
>
> > A column holds a type of value. Any kind. The
> > structure and operands define the type. The data
> > defines the value. This holds true for simple types
> > like an integer or complex types like an array.
> >
> > The database data is relatively "type blind" in an
> > ORDBMS. It uses the standard overloaded operands
> > to determine the type of function to perform for
> > all of the usual RDBMS utilities.
> > Constraints, triggers, sorting, etc. all apply.
> >
> > That's what the ORDBMS stuff can give you.
> > Arrays are a natural extension.
> >
> > Arrays don't necessarily imply denormalization.
> > It depends on how you use them. The same rule
> > applies for integers.
>
> I dunno 'bout that...
>
> > elein
> >
> > On Fri, Aug 15, 2003 at 01:13:52PM -0500, Ron Johnson wrote:
> > >
> > > Why are arrays even mentioned in the the same breath wrt relations
> > > DBMSs? Aren't they an anathema to all we know and love?
>
> --
> +---------------------------------------------------------------+
> | Ron Johnson, Jr. Home: ron(dot)l(dot)johnson(at)cox(dot)net |
> | Jefferson, LA USA |
> | |
> | "Man, I'm pretty. Hoo Hah!" |
> | Johnny Bravo |
> +---------------------------------------------------------------+
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

On Friday 15 August 2003 02:56 pm, elein wrote:
> In response to both Andrew Gould and Ron Johnson...
>
> If arrays are not natural in the organization of
> your data, don't use them. That is the guideline.
>
> If the array defines something specific they are
> very natural. The confusion could be that arrays
> are abstract types.
>
> Specific implementations which use arrays might
> be clearer. For example, a definition of a polygon
> is an array of Points. Points, themselves are an
> array.
>
> (The actual postgreSQL implementation of polygons and points
> doesn't use the newer cleaner array abstraction, I think.
> But if I were reimplementing them, I would build on
> top of the new array capabilities. The point is to show
> an array structured object which makes sense in context.)
>
> Of course you can denomalize via arrays, but it tends
> to make things harder for you. And I believe the
> same thing is true for denormalized integer columns.
>
> elein
> =============================================================
> elein(at)varlena(dot)com www.varlena.com

Thanks, Elein. The polygon example makes it clearer. In the books I have
here, the examples show how to use arrays but they use data that I would move
to another table.

Best regards,

Andrew

On Fri, 2003-08-15 at 15:36, Andrew L. Gould wrote:
> On Friday 15 August 2003 02:56 pm, elein wrote:
> > In response to both Andrew Gould and Ron Johnson...
> >
> > If arrays are not natural in the organization of
> > your data, don't use them. That is the guideline.
> >
> > If the array defines something specific they are
> > very natural. The confusion could be that arrays
> > are abstract types.
> >
> > Specific implementations which use arrays might
> > be clearer. For example, a definition of a polygon
> > is an array of Points. Points, themselves are an
> > array.
> >
> > (The actual postgreSQL implementation of polygons and points
> > doesn't use the newer cleaner array abstraction, I think.
> > But if I were reimplementing them, I would build on
> > top of the new array capabilities. The point is to show
> > an array structured object which makes sense in context.)
> >
> > Of course you can denomalize via arrays, but it tends
> > to make things harder for you. And I believe the
> > same thing is true for denormalized integer columns.
> >
> > elein
> > =============================================================
> > elein(at)varlena(dot)com www.varlena.com
>
> Thanks, Elein. The polygon example makes it clearer. In the books I have
> here, the examples show how to use arrays but they use data that I would move
> to another table.

This is what makes me nervous about db arrays: the tendency for
denormalization.

--
+---------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron(dot)l(dot)johnson(at)cox(dot)net |
| Jefferson, LA USA |
| |
| "Man, I'm pretty. Hoo Hah!" |
| Johnny Bravo |
+---------------------------------------------------------------+

Tom Lane wrote:
> Could you look at how big a change it'd be, anyway? Offhand I think it
> may just mean that the subscript-checking done in parse_expr.c needs to
> be done at runtime instead. Remember parse_expr should only be
> concerned about determining datatype, and for its purposes all arrays of
> a given element type are the same --- subscript checking should happen
> at runtime. (It seems likely that having an ndims field in ArrayExpr
> is inappropriate.)

The attached patch fixes code and regression tests for the following
(docs to follow once applied):

========================================================================
1) Array concatenation of equidimensional arrays:
========================================================================
regression=# select ARRAY[1,2] || ARRAY[3,4];
?column?
-----------
{1,2,3,4}
(1 row)

regression=# select ARRAY[[1],[2],[3]] || ARRAY[[4],[5]];
?column?
-----------------------
{{1},{2},{3},{4},{5}}
(1 row)

regression=# select ARRAY[[1,2],[2,3],[3,4]] || ARRAY[[4,5],[5,6]];
?column?
---------------------------------
{{1,2},{2,3},{3,4},{4,5},{5,6}}
(1 row)

========================================================================
2) Array literals or vars in ARRAY expressions:
========================================================================
regression=# create table arr(f1 int[], f2 int[]);
CREATE TABLE
regression=# insert into arr values (ARRAY[[1,2],[3,4]],ARRAY[[5,6],[7,8]]);
INSERT 2635544 1
regression=# select ARRAY[f1,f2] from arr;
array
-------------------------------
{{{1,2},{3,4}},{{5,6},{7,8}}}
(1 row)

regression=# select ARRAY['{{1,2},{3,4}}'::int[],'{{5,6},{7,8}}'::int[]];
array
-------------------------------
{{{1,2},{3,4}},{{5,6},{7,8}}}
(1 row)

========================================================================
3) Lower bound of outer array adjusted downward when an "element" (which
could itself be an array) is concatenated onto the front of an array:
========================================================================
regression=# create table arr(f1 int[]);
CREATE TABLE
regression=# insert into arr values ('{}');
INSERT 2635538 1
regression=# update arr set f1[-2] = 1;
UPDATE 1
regression=# select array_lower(f1,1) from arr;
array_lower
-------------
-2
(1 row)

regression=# select array_lower(f1 || 2, 1) from arr;
array_lower
-------------
-2
(1 row)

regression=# select array_lower(0 || f1, 1) from arr;
array_lower
-------------
-3
(1 row)

regression=# update arr set f1 = ARRAY[[1,2],[3,4]];
UPDATE 1
regression=# select array_lower(f1,1) from arr;
array_lower
-------------
1
(1 row)

regression=# select array_lower(f1 || ARRAY[5,6], 1) from arr;
array_lower
-------------
1
(1 row)

regression=# select array_lower(ARRAY[-1,0] || f1, 1) from arr;
array_lower
-------------
0
(1 row)

Compiles without warnings and passes all regression tests. If there are
no objections, please apply. As I mentioned above, docs to follow once
I'm sure what actually ends up being committed.

Joe

create table import_contact (
id character(7) not null primary key,
fm character(30),
ls character(30),
addr character(30),
city character(25),
st character(2),
c character(1),
start decimal(6),
end decimal(6),
) WITHOUT OIDS;

cat datafile.dat | psql -dthedatabase -c "copy import_contact from stdin
delimiter ',' null ''";

echo "insert into contact select
id,
case fm when null then 'xzxzxzxz' else fm end,
case ls when null then 'xzxzxzxz' else ls end,
case addr when null then '123 xzxzxzxz' else addr end,
case city when null then 'xzxzxzxz' else city end,
case st when null then 'xz' else st end,
case c when null then 'x' else c end,
case start when null then 122038 else start,
case end when null then 122038 else end
from import_contact;" | psql -dthedatabase

Could be one way although it's not atomic. Can rewrite the copy command to be
a copy from file command to do that and use the \i command (or redirect to
psql from file/stdin). Simple but there are many other methods to get this
thing to work. If you don't want to recreate the defaults everytime then you
could have subselects that reference the pg system tables extract the default
value for the columns you are looking for.

Also could create the insert statements with a script on the outside or
replace any blank (null in reality) fields with the default value and copy
that straight to the table.

On Sat, 16 Aug 2003 03:18 am, expect wrote:
> I'd like to summarize what I know (or don't know) since this topic has been
> hit around a little and I'm new to this. I'm hoping it will clear things
> up, at least for me. You are all the experts, I want to make sure I am
> singing from the same page.
>
> data sample:
> id | fm | ls | addr | city | st | z |c|
> start|end
> ---------------------------------------------------------------------------
>-------
>
> 191922C,Bob Cobb,D'Obbalina Sr.,312 Elm
> Street,Yountville,CA,94599,5,062001,082009 339111C,Elma Thelma,Velma,98 Oak
> Lane,St. Louis,MO,63119-2065,,,
>
>
> What I wanted to do was to import lots of these from a text file. In the
> case where there is an empty string (i.e. no value after a comma) I wanted
> to define the column in the table in a way that would accept the empty
> string but replace it with the default value for that column. I didn't
> know that the copy command is just some C code that stuffs the data into
> the db ala fois grois.
>
> What I would really benefit from (and I hope some other new soul would too)
> is if someone would outline exactly how they would approach this problem.
>
> Maybe provide the correct table definition and the copy command. Or if
> that just won't work an alternate approach. I realize that some of you
> have done this partially but there have been too many replies to get into a
> single cohesive instruction.
>
>
> Anyway I suppose my initial frustration in trying to do this may have
> blinded me from reason.
>
>
> create table contact (
> id character(7) NOT NULL,
> fm character(30) DEFAULT 'xzxzxzxz',
> ls character(30) DEFAULT 'xzxzxzxz',
> addr character(30) DEFAULT '123 xzxzxzxz',
> city character(25) DEFAULT 'xzxzxzxz',
> st character(2) DEFAULT 'xz',
> c character(1) DEFAULT 'x',
> start decimal(6) DEFAULT 122038,
> end decimal(6) DEFAULT 122038,
> CONSTRAINT handle PRIMARY KEY (id)
> ) WITHOUT OIDS;
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

On Sat, 16 Aug 2003 09:33:51 +1000
Jason Godden <jasongodden(at)optushome(dot)com(dot)au> wrote:

Ahh, thanks for this. And thanks to all the others that helped me on my way.
Hopefully I'll be able to give something back to the group. Although that
might be hard with all the experts here.

Perhaps I can document this and provide it for public consumption.

> create table import_contact (
> id character(7) not null primary key,
> fm character(30),
> ls character(30),
> addr character(30),
> city character(25),
> st character(2),
> c character(1),
> start decimal(6),
> end decimal(6),
> ) WITHOUT OIDS;
>
> cat datafile.dat | psql -dthedatabase -c "copy import_contact from stdin
> delimiter ',' null ''";
>
> echo "insert into contact select
> id,
> case fm when null then 'xzxzxzxz' else fm end,
> case ls when null then 'xzxzxzxz' else ls end,
> case addr when null then '123 xzxzxzxz' else addr end,
> case city when null then 'xzxzxzxz' else city end,
> case st when null then 'xz' else st end,
> case c when null then 'x' else c end,
> case start when null then 122038 else start,
> case end when null then 122038 else end
> from import_contact;" | psql -dthedatabase
>
> Could be one way although it's not atomic. Can rewrite the copy command to be
> a copy from file command to do that and use the \i command (or redirect to
> psql from file/stdin). Simple but there are many other methods to get this
> thing to work. If you don't want to recreate the defaults everytime then you
> could have subselects that reference the pg system tables extract the default
> value for the columns you are looking for.
>
> Also could create the insert statements with a script on the outside or
> replace any blank (null in reality) fields with the default value and copy
> that straight to the table.
>
> On Sat, 16 Aug 2003 03:18 am, expect wrote:
> > I'd like to summarize what I know (or don't know) since this topic has been
> > hit around a little and I'm new to this. I'm hoping it will clear things
> > up, at least for me. You are all the experts, I want to make sure I am
> > singing from the same page.
> >
> > data sample:
> > id | fm | ls | addr | city | st | z |c|
> > start|end
> > ---------------------------------------------------------------------------
> >-------
> >
> > 191922C,Bob Cobb,D'Obbalina Sr.,312 Elm
> > Street,Yountville,CA,94599,5,062001,082009 339111C,Elma Thelma,Velma,98 Oak
> > Lane,St. Louis,MO,63119-2065,,,
> >
> >
> > What I wanted to do was to import lots of these from a text file. In the
> > case where there is an empty string (i.e. no value after a comma) I wanted
> > to define the column in the table in a way that would accept the empty
> > string but replace it with the default value for that column. I didn't
> > know that the copy command is just some C code that stuffs the data into
> > the db ala fois grois.
> >
> > What I would really benefit from (and I hope some other new soul would too)
> > is if someone would outline exactly how they would approach this problem.
> >
> > Maybe provide the correct table definition and the copy command. Or if
> > that just won't work an alternate approach. I realize that some of you
> > have done this partially but there have been too many replies to get into a
> > single cohesive instruction.
> >
> >
> > Anyway I suppose my initial frustration in trying to do this may have
> > blinded me from reason.
> >
> >
> > create table contact (
> > id character(7) NOT NULL,
> > fm character(30) DEFAULT 'xzxzxzxz',
> > ls character(30) DEFAULT 'xzxzxzxz',
> > addr character(30) DEFAULT '123 xzxzxzxz',
> > city character(25) DEFAULT 'xzxzxzxz',
> > st character(2) DEFAULT 'xz',
> > c character(1) DEFAULT 'x',
> > start decimal(6) DEFAULT 122038,
> > end decimal(6) DEFAULT 122038,
> > CONSTRAINT handle PRIMARY KEY (id)
> > ) WITHOUT OIDS;
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>

Just a note on that example:

I didn't properly end the case commands on the last two fields in the insert
and end should probably be quoted. No I haven't tested it. Should be:

echo "insert into contact select
id,
case fm when null then 'xzxzxzxz' else fm end,
case ls when null then 'xzxzxzxz' else ls end,
case addr when null then '123 xzxzxzxz' else addr end,
case city when null then 'xzxzxzxz' else city end,
case st when null then 'xz' else st end,
case c when null then 'x' else c end,
case start when null then 122038 else start end,
case "end" when null then 122038 else "end" end
from import_contact;" | psql -dthedatabase

Rgds,

Jason

On Sat, 16 Aug 2003 01:10 pm, expect wrote:
> On Sat, 16 Aug 2003 09:33:51 +1000
> Jason Godden <jasongodden(at)optushome(dot)com(dot)au> wrote:
>
>
> Ahh, thanks for this. And thanks to all the others that helped me on my
> way. Hopefully I'll be able to give something back to the group. Although
> that might be hard with all the experts here.
>
> Perhaps I can document this and provide it for public consumption.
>
> > create table import_contact (
> > id character(7) not null primary key,
> > fm character(30),
> > ls character(30),
> > addr character(30),
> > city character(25),
> > st character(2),
> > c character(1),
> > start decimal(6),
> > end decimal(6),
> > ) WITHOUT OIDS;
> >
> > cat datafile.dat | psql -dthedatabase -c "copy import_contact from stdin
> > delimiter ',' null ''";
> >
> > echo "insert into contact select
> > id,
> > case fm when null then 'xzxzxzxz' else fm end,
> > case ls when null then 'xzxzxzxz' else ls end,
> > case addr when null then '123 xzxzxzxz' else addr end,
> > case city when null then 'xzxzxzxz' else city end,
> > case st when null then 'xz' else st end,
> > case c when null then 'x' else c end,
> > case start when null then 122038 else start,
> > case end when null then 122038 else end
> > from import_contact;" | psql -dthedatabase
> >
> > Could be one way although it's not atomic. Can rewrite the copy command
> > to be a copy from file command to do that and use the \i command (or
> > redirect to psql from file/stdin). Simple but there are many other
> > methods to get this thing to work. If you don't want to recreate the
> > defaults everytime then you could have subselects that reference the pg
> > system tables extract the default value for the columns you are looking
> > for.
> >
> > Also could create the insert statements with a script on the outside or
> > replace any blank (null in reality) fields with the default value and
> > copy that straight to the table.
> >
> > On Sat, 16 Aug 2003 03:18 am, expect wrote:
> > > I'd like to summarize what I know (or don't know) since this topic has
> > > been hit around a little and I'm new to this. I'm hoping it will clear
> > > things up, at least for me. You are all the experts, I want to make
> > > sure I am singing from the same page.
> > >
> > > data sample:
> > > id | fm | ls | addr | city | st | z |c|
> > > start|end
> > > -----------------------------------------------------------------------
> > >---- -------
> > >
> > > 191922C,Bob Cobb,D'Obbalina Sr.,312 Elm
> > > Street,Yountville,CA,94599,5,062001,082009 339111C,Elma Thelma,Velma,98
> > > Oak Lane,St. Louis,MO,63119-2065,,,
> > >
> > >
> > > What I wanted to do was to import lots of these from a text file. In
> > > the case where there is an empty string (i.e. no value after a comma)
> > > I wanted to define the column in the table in a way that would accept
> > > the empty string but replace it with the default value for that column.
> > > I didn't know that the copy command is just some C code that stuffs
> > > the data into the db ala fois grois.
> > >
> > > What I would really benefit from (and I hope some other new soul would
> > > too) is if someone would outline exactly how they would approach this
> > > problem.
> > >
> > > Maybe provide the correct table definition and the copy command. Or if
> > > that just won't work an alternate approach. I realize that some of you
> > > have done this partially but there have been too many replies to get
> > > into a single cohesive instruction.
> > >
> > >
> > > Anyway I suppose my initial frustration in trying to do this may have
> > > blinded me from reason.
> > >
> > >
> > > create table contact (
> > > id character(7) NOT NULL,
> > > fm character(30) DEFAULT 'xzxzxzxz',
> > > ls character(30) DEFAULT 'xzxzxzxz',
> > > addr character(30) DEFAULT '123 xzxzxzxz',
> > > city character(25) DEFAULT 'xzxzxzxz',
> > > st character(2) DEFAULT 'xz',
> > > c character(1) DEFAULT 'x',
> > > start decimal(6) DEFAULT 122038,
> > > end decimal(6) DEFAULT 122038,
> > > CONSTRAINT handle PRIMARY KEY (id)
> > > ) WITHOUT OIDS;
> > >
> > >
> > > ---------------------------(end of
> > > broadcast)--------------------------- TIP 2: you can get off all lists
> > > at once with the unregister command (send "unregister
> > > YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Joe Conway wrote:
> Tom Lane wrote:
> > Could you look at how big a change it'd be, anyway? Offhand I think it
> > may just mean that the subscript-checking done in parse_expr.c needs to
> > be done at runtime instead. Remember parse_expr should only be
> > concerned about determining datatype, and for its purposes all arrays of
> > a given element type are the same --- subscript checking should happen
> > at runtime. (It seems likely that having an ndims field in ArrayExpr
> > is inappropriate.)
>
> The attached patch fixes code and regression tests for the following
> (docs to follow once applied):
>
> ========================================================================
> 1) Array concatenation of equidimensional arrays:
> ========================================================================
> regression=# select ARRAY[1,2] || ARRAY[3,4];
> ?column?
> -----------
> {1,2,3,4}
> (1 row)
>
> regression=# select ARRAY[[1],[2],[3]] || ARRAY[[4],[5]];
> ?column?
> -----------------------
> {{1},{2},{3},{4},{5}}
> (1 row)
>
> regression=# select ARRAY[[1,2],[2,3],[3,4]] || ARRAY[[4,5],[5,6]];
> ?column?
> ---------------------------------
> {{1,2},{2,3},{3,4},{4,5},{5,6}}
> (1 row)
>
>
> ========================================================================
> 2) Array literals or vars in ARRAY expressions:
> ========================================================================
> regression=# create table arr(f1 int[], f2 int[]);
> CREATE TABLE
> regression=# insert into arr values (ARRAY[[1,2],[3,4]],ARRAY[[5,6],[7,8]]);
> INSERT 2635544 1
> regression=# select ARRAY[f1,f2] from arr;
> array
> -------------------------------
> {{{1,2},{3,4}},{{5,6},{7,8}}}
> (1 row)
>
> regression=# select ARRAY['{{1,2},{3,4}}'::int[],'{{5,6},{7,8}}'::int[]];
> array
> -------------------------------
> {{{1,2},{3,4}},{{5,6},{7,8}}}
> (1 row)
>
>
> ========================================================================
> 3) Lower bound of outer array adjusted downward when an "element" (which
> could itself be an array) is concatenated onto the front of an array:
> ========================================================================
> regression=# create table arr(f1 int[]);
> CREATE TABLE
> regression=# insert into arr values ('{}');
> INSERT 2635538 1
> regression=# update arr set f1[-2] = 1;
> UPDATE 1
> regression=# select array_lower(f1,1) from arr;
> array_lower
> -------------
> -2
> (1 row)
>
> regression=# select array_lower(f1 || 2, 1) from arr;
> array_lower
> -------------
> -2
> (1 row)
>
> regression=# select array_lower(0 || f1, 1) from arr;
> array_lower
> -------------
> -3
> (1 row)
>
> regression=# update arr set f1 = ARRAY[[1,2],[3,4]];
> UPDATE 1
> regression=# select array_lower(f1,1) from arr;
> array_lower
> -------------
> 1
> (1 row)
>
> regression=# select array_lower(f1 || ARRAY[5,6], 1) from arr;
> array_lower
> -------------
> 1
> (1 row)
>
> regression=# select array_lower(ARRAY[-1,0] || f1, 1) from arr;
> array_lower
> -------------
> 0
> (1 row)
>
>
> Compiles without warnings and passes all regression tests. If there are
> no objections, please apply. As I mentioned above, docs to follow once
> I'm sure what actually ends up being committed.
>
> Joe

> Index: src/backend/executor/execQual.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/executor/execQual.c,v
> retrieving revision 1.141
> diff -c -r1.141 execQual.c
> *** src/backend/executor/execQual.c 8 Aug 2003 21:41:39 -0000 1.141
> --- src/backend/executor/execQual.c 15 Aug 2003 21:52:30 -0000
> ***************
> *** 1620,1635 ****
> ArrayType *result;
> List *element;
> Oid element_type = arrayExpr->element_typeid;
> ! int ndims = arrayExpr->ndims;
> int dims[MAXDIM];
> int lbs[MAXDIM];
>
> ! if (ndims == 1)
> {
> int nelems;
> Datum *dvalues;
> int i = 0;
>
> nelems = length(astate->elements);
>
> /* Shouldn't happen here, but if length is 0, return NULL */
> --- 1620,1637 ----
> ArrayType *result;
> List *element;
> Oid element_type = arrayExpr->element_typeid;
> ! int ndims = 0;
> int dims[MAXDIM];
> int lbs[MAXDIM];
>
> ! if (!arrayExpr->multidims)
> {
> + /* Elements are presumably of scalar type */
> int nelems;
> Datum *dvalues;
> int i = 0;
>
> + ndims = 1;
> nelems = length(astate->elements);
>
> /* Shouldn't happen here, but if length is 0, return NULL */
> ***************
> *** 1667,1672 ****
> --- 1669,1675 ----
> }
> else
> {
> + /* Must be nested array expressions */
> char *dat = NULL;
> Size ndatabytes = 0;
> int nbytes;
> ***************
> *** 1677,1688 ****
> bool firstone = true;
> int i;
>
> - if (ndims <= 0 || ndims > MAXDIM)
> - ereport(ERROR,
> - (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
> - errmsg("number of array dimensions exceeds the maximum allowed, %d",
> - MAXDIM)));
> -
> /* loop through and get data area from each element */
> foreach(element, astate->elements)
> {
> --- 1680,1685 ----
> ***************
> *** 1705,1714 ****
> --- 1702,1719 ----
> {
> /* Get sub-array details from first member */
> elem_ndims = ARR_NDIM(array);
> + ndims = elem_ndims + 1;
> + if (ndims <= 0 || ndims > MAXDIM)
> + ereport(ERROR,
> + (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
> + errmsg("number of array dimensions exceeds " \
> + "the maximum allowed, %d", MAXDIM)));
> +
> elem_dims = (int *) palloc(elem_ndims * sizeof(int));
> memcpy(elem_dims, ARR_DIMS(array), elem_ndims * sizeof(int));
> elem_lbs = (int *) palloc(elem_ndims * sizeof(int));
> memcpy(elem_lbs, ARR_LBOUND(array), elem_ndims * sizeof(int));
> +
> firstone = false;
> }
> else
> Index: src/backend/nodes/copyfuncs.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/nodes/copyfuncs.c,v
> retrieving revision 1.263
> diff -c -r1.263 copyfuncs.c
> *** src/backend/nodes/copyfuncs.c 8 Aug 2003 21:41:43 -0000 1.263
> --- src/backend/nodes/copyfuncs.c 15 Aug 2003 21:44:30 -0000
> ***************
> *** 947,953 ****
> COPY_SCALAR_FIELD(array_typeid);
> COPY_SCALAR_FIELD(element_typeid);
> COPY_NODE_FIELD(elements);
> ! COPY_SCALAR_FIELD(ndims);
>
> return newnode;
> }
> --- 947,953 ----
> COPY_SCALAR_FIELD(array_typeid);
> COPY_SCALAR_FIELD(element_typeid);
> COPY_NODE_FIELD(elements);
> ! COPY_SCALAR_FIELD(multidims);
>
> return newnode;
> }
> Index: src/backend/nodes/equalfuncs.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/nodes/equalfuncs.c,v
> retrieving revision 1.207
> diff -c -r1.207 equalfuncs.c
> *** src/backend/nodes/equalfuncs.c 8 Aug 2003 21:41:43 -0000 1.207
> --- src/backend/nodes/equalfuncs.c 15 Aug 2003 21:44:44 -0000
> ***************
> *** 409,415 ****
> COMPARE_SCALAR_FIELD(array_typeid);
> COMPARE_SCALAR_FIELD(element_typeid);
> COMPARE_NODE_FIELD(elements);
> ! COMPARE_SCALAR_FIELD(ndims);
>
> return true;
> }
> --- 409,415 ----
> COMPARE_SCALAR_FIELD(array_typeid);
> COMPARE_SCALAR_FIELD(element_typeid);
> COMPARE_NODE_FIELD(elements);
> ! COMPARE_SCALAR_FIELD(multidims);
>
> return true;
> }
> Index: src/backend/nodes/outfuncs.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/nodes/outfuncs.c,v
> retrieving revision 1.217
> diff -c -r1.217 outfuncs.c
> *** src/backend/nodes/outfuncs.c 8 Aug 2003 21:41:44 -0000 1.217
> --- src/backend/nodes/outfuncs.c 15 Aug 2003 21:45:34 -0000
> ***************
> *** 785,791 ****
> WRITE_OID_FIELD(array_typeid);
> WRITE_OID_FIELD(element_typeid);
> WRITE_NODE_FIELD(elements);
> ! WRITE_INT_FIELD(ndims);
> }
>
> static void
> --- 785,791 ----
> WRITE_OID_FIELD(array_typeid);
> WRITE_OID_FIELD(element_typeid);
> WRITE_NODE_FIELD(elements);
> ! WRITE_BOOL_FIELD(multidims);
> }
>
> static void
> Index: src/backend/nodes/readfuncs.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/nodes/readfuncs.c,v
> retrieving revision 1.161
> diff -c -r1.161 readfuncs.c
> *** src/backend/nodes/readfuncs.c 4 Aug 2003 02:39:59 -0000 1.161
> --- src/backend/nodes/readfuncs.c 15 Aug 2003 21:46:05 -0000
> ***************
> *** 659,665 ****
> READ_OID_FIELD(array_typeid);
> READ_OID_FIELD(element_typeid);
> READ_NODE_FIELD(elements);
> ! READ_INT_FIELD(ndims);
>
> READ_DONE();
> }
> --- 659,665 ----
> READ_OID_FIELD(array_typeid);
> READ_OID_FIELD(element_typeid);
> READ_NODE_FIELD(elements);
> ! READ_BOOL_FIELD(multidims);
>
> READ_DONE();
> }
> Index: src/backend/optimizer/util/clauses.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/optimizer/util/clauses.c,v
> retrieving revision 1.152
> diff -c -r1.152 clauses.c
> *** src/backend/optimizer/util/clauses.c 8 Aug 2003 21:41:55 -0000 1.152
> --- src/backend/optimizer/util/clauses.c 15 Aug 2003 21:48:03 -0000
> ***************
> *** 1515,1521 ****
> newarray->array_typeid = arrayexpr->array_typeid;
> newarray->element_typeid = arrayexpr->element_typeid;
> newarray->elements = FastListValue(&newelems);
> ! newarray->ndims = arrayexpr->ndims;
>
> if (all_const)
> return (Node *) evaluate_expr((Expr *) newarray,
> --- 1515,1521 ----
> newarray->array_typeid = arrayexpr->array_typeid;
> newarray->element_typeid = arrayexpr->element_typeid;
> newarray->elements = FastListValue(&newelems);
> ! newarray->multidims = arrayexpr->multidims;
>
> if (all_const)
> return (Node *) evaluate_expr((Expr *) newarray,
> Index: src/backend/parser/parse_expr.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_expr.c,v
> retrieving revision 1.160
> diff -c -r1.160 parse_expr.c
> *** src/backend/parser/parse_expr.c 4 Aug 2003 02:40:01 -0000 1.160
> --- src/backend/parser/parse_expr.c 15 Aug 2003 21:38:10 -0000
> ***************
> *** 748,754 ****
> List *element;
> Oid array_type;
> Oid element_type;
> - int ndims;
>
> /* Transform the element expressions */
> foreach(element, a->elements)
> --- 748,753 ----
> ***************
> *** 781,791 ****
> if (array_type != InvalidOid)
> {
> /* Elements are presumably of scalar type */
> ! ndims = 1;
> }
> else
> {
> /* Must be nested array expressions */
> array_type = element_type;
> element_type = get_element_type(array_type);
> if (!OidIsValid(element_type))
> --- 780,792 ----
> if (array_type != InvalidOid)
> {
> /* Elements are presumably of scalar type */
> ! newa->multidims = false;
> }
> else
> {
> /* Must be nested array expressions */
> + newa->multidims = true;
> +
> array_type = element_type;
> element_type = get_element_type(array_type);
> if (!OidIsValid(element_type))
> ***************
> *** 793,839 ****
> (errcode(ERRCODE_UNDEFINED_OBJECT),
> errmsg("could not find array type for datatype %s",
> format_type_be(array_type))));
> -
> - /*
> - * make sure the element expressions all have the same
> - * number of dimensions
> - */
> - ndims = 0;
> - foreach(element, newcoercedelems)
> - {
> - ArrayExpr *e = (ArrayExpr *) lfirst(element);
> -
> - if (!IsA(e, ArrayExpr))
> - ereport(ERROR,
> - (errcode(ERRCODE_SYNTAX_ERROR),
> - errmsg("multidimensional ARRAY[] must be built from nested array expressions")));
> - if (ndims == 0)
> - ndims = e->ndims;
> - else if (e->ndims != ndims)
> - ereport(ERROR,
> - (errcode(ERRCODE_SYNTAX_ERROR),
> - errmsg("nested array expressions must have common number of dimensions")));
> - if (e->element_typeid != element_type)
> - ereport(ERROR,
> - (errcode(ERRCODE_SYNTAX_ERROR),
> - errmsg("nested array expressions must have common element type")));
> -
> - }
> - /* increment the number of dimensions */
> - ndims++;
> -
> - /* make sure we don't have too many dimensions now */
> - if (ndims > MAXDIM)
> - ereport(ERROR,
> - (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
> - errmsg("number of array dimensions exceeds the maximum allowed, %d",
> - MAXDIM)));
> }
>
> newa->array_typeid = array_type;
> newa->element_typeid = element_type;
> newa->elements = newcoercedelems;
> - newa->ndims = ndims;
>
> result = (Node *) newa;
> break;
> --- 794,804 ----
> Index: src/backend/utils/adt/array_userfuncs.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/adt/array_userfuncs.c,v
> retrieving revision 1.7
> diff -c -r1.7 array_userfuncs.c
> *** src/backend/utils/adt/array_userfuncs.c 4 Aug 2003 00:43:25 -0000 1.7
> --- src/backend/utils/adt/array_userfuncs.c 15 Aug 2003 22:41:12 -0000
> ***************
> *** 132,138 ****
>
> /*-----------------------------------------------------------------------------
> * array_cat :
> ! * concatenate two nD arrays to form an (n+1)D array, or
> * push an (n-1)D array onto the end of an nD array
> *----------------------------------------------------------------------------
> */
> --- 132,138 ----
>
> /*-----------------------------------------------------------------------------
> * array_cat :
> ! * concatenate two nD arrays to form an nD array, or
> * push an (n-1)D array onto the end of an nD array
> *----------------------------------------------------------------------------
> */
> ***************
> *** 223,251 ****
> if (ndims1 == ndims2)
> {
> /*
> ! * resulting array has two element outer array made up of input
> ! * argument arrays
> */
> int i;
>
> ! ndims = ndims1 + 1;
> dims = (int *) palloc(ndims * sizeof(int));
> lbs = (int *) palloc(ndims * sizeof(int));
>
> ! dims[0] = 2; /* outer array made up of two input arrays */
> ! lbs[0] = 1; /* start lower bound at 1 */
>
> ! for (i = 0; i < ndims1; i++)
> {
> if (dims1[i] != dims2[i] || lbs1[i] != lbs2[i])
> ereport(ERROR,
> (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
> errmsg("cannot concatenate incompatible arrays"),
> ! errdetail("Arrays with differing dimensions are not "
> ! "compatible for concatenation.")));
>
> ! dims[i + 1] = dims1[i];
> ! lbs[i + 1] = lbs1[i];
> }
> }
> else if (ndims1 == ndims2 - 1)
> --- 223,251 ----
> if (ndims1 == ndims2)
> {
> /*
> ! * resulting array is made up of the elements (possibly arrays themselves)
> ! * of the input argument arrays
> */
> int i;
>
> ! ndims = ndims1;
> dims = (int *) palloc(ndims * sizeof(int));
> lbs = (int *) palloc(ndims * sizeof(int));
>
> ! dims[0] = dims1[0] + dims2[0];
> ! lbs[0] = lbs1[0];
>
> ! for (i = 1; i < ndims; i++)
> {
> if (dims1[i] != dims2[i] || lbs1[i] != lbs2[i])
> ereport(ERROR,
> (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
> errmsg("cannot concatenate incompatible arrays"),
> ! errdetail("Arrays with differing element dimensions are "
> ! "not compatible for concatenation.")));
>
> ! dims[i] = dims1[i];
> ! lbs[i] = lbs1[i];
> }
> }
> else if (ndims1 == ndims2 - 1)
> ***************
> *** 264,269 ****
> --- 264,272 ----
> /* increment number of elements in outer array */
> dims[0] += 1;
>
> + /* decrement outer array lower bound */
> + lbs[0] -= 1;
> +
> /* make sure the added element matches our existing elements */
> for (i = 0; i < ndims1; i++)
> {
> ***************
> *** 276,284 ****
> }
> }
> else
> - /* (ndims1 == ndims2 + 1) */
> {
> ! /*
> * resulting array has the first argument as the outer array, with
> * the second argument appended to the end of the outer dimension
> */
> --- 279,287 ----
> }
> }
> else
> {
> ! /* (ndims1 == ndims2 + 1)
> ! *
> * resulting array has the first argument as the outer array, with
> * the second argument appended to the end of the outer dimension
> */
> Index: src/include/nodes/primnodes.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/include/nodes/primnodes.h,v
> retrieving revision 1.91
> diff -c -r1.91 primnodes.h
> *** src/include/nodes/primnodes.h 11 Aug 2003 23:04:50 -0000 1.91
> --- src/include/nodes/primnodes.h 15 Aug 2003 21:37:35 -0000
> ***************
> *** 596,602 ****
> Oid array_typeid; /* type of expression result */
> Oid element_typeid; /* common type of expression elements */
> List *elements; /* the array elements */
> ! int ndims; /* number of array dimensions */
> } ArrayExpr;
>
> /*
> --- 596,602 ----
> Oid array_typeid; /* type of expression result */
> Oid element_typeid; /* common type of expression elements */
> List *elements; /* the array elements */
> ! bool multidims; /* true if elements are also ArrayExprs */
> } ArrayExpr;
>
> /*
> Index: src/test/regress/expected/arrays.out
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/arrays.out,v
> retrieving revision 1.17
> diff -c -r1.17 arrays.out
> *** src/test/regress/expected/arrays.out 21 Jul 2003 20:29:40 -0000 1.17
> --- src/test/regress/expected/arrays.out 15 Aug 2003 23:09:19 -0000
> ***************
> *** 190,199 ****
> {6,42}
> (1 row)
>
> ! SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{{1,2},{3,4}}";
> ! {{1,2},{3,4}}
> ! ---------------
> ! {{1,2},{3,4}}
> (1 row)
>
> SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}";
> --- 190,199 ----
> {6,42}
> (1 row)
>
> ! SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{1,2,3,4}";
> ! {1,2,3,4}
> ! -----------
> ! {1,2,3,4}
> (1 row)
>
> SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}";
> ***************
> *** 233,248 ****
> {0,1,2}
> (1 row)
>
> ! SELECT ARRAY[1,2] || ARRAY[3,4] AS "{{1,2},{3,4}}";
> ! {{1,2},{3,4}}
> ! ---------------
> ! {{1,2},{3,4}}
> (1 row)
>
> SELECT ARRAY[[['hello','world']]] || ARRAY[[['happy','birthday']]] AS "ARRAY";
> ! ARRAY
> ! ------------------------------------------
> ! {{{{hello,world}}},{{{happy,birthday}}}}
> (1 row)
>
> SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}";
> --- 233,248 ----
> {0,1,2}
> (1 row)
>
> ! SELECT ARRAY[1,2] || ARRAY[3,4] AS "{1,2,3,4}";
> ! {1,2,3,4}
> ! -----------
> ! {1,2,3,4}
> (1 row)
>
> SELECT ARRAY[[['hello','world']]] || ARRAY[[['happy','birthday']]] AS "ARRAY";
> ! ARRAY
> ! --------------------------------------
> ! {{{hello,world}},{{happy,birthday}}}
> (1 row)
>
> SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}";
> ***************
> *** 251,260 ****
> {{1,2},{3,4},{5,6}}
> (1 row)
>
> ! SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{{0,0},{1,1},{2,2}}";
> ! {{0,0},{1,1},{2,2}}
> ! ---------------------
> ! {{0,0},{1,1},{2,2}}
> (1 row)
>
> SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}";
> --- 251,260 ----
> {{1,2},{3,4},{5,6}}
> (1 row)
>
> ! SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{0,0,1,1,2,2}";
> ! {0,0,1,1,2,2}
> ! ---------------
> ! {0,0,1,1,2,2}
> (1 row)
>
> SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}";
> Index: src/test/regress/sql/arrays.sql
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/test/regress/sql/arrays.sql,v
> retrieving revision 1.14
> diff -c -r1.14 arrays.sql
> *** src/test/regress/sql/arrays.sql 29 Jun 2003 00:33:44 -0000 1.14
> --- src/test/regress/sql/arrays.sql 15 Aug 2003 23:02:06 -0000
> ***************
> *** 132,138 ****
> -- functions
> SELECT array_append(array[42], 6) AS "{42,6}";
> SELECT array_prepend(6, array[42]) AS "{6,42}";
> ! SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{{1,2},{3,4}}";
> SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}";
> SELECT array_cat(ARRAY[[3,4],[5,6]], ARRAY[1,2]) AS "{{3,4},{5,6},{1,2}}";
>
> --- 132,138 ----
> -- functions
> SELECT array_append(array[42], 6) AS "{42,6}";
> SELECT array_prepend(6, array[42]) AS "{6,42}";
> ! SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{1,2,3,4}";
> SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}";
> SELECT array_cat(ARRAY[[3,4],[5,6]], ARRAY[1,2]) AS "{{3,4},{5,6},{1,2}}";
>
> ***************
> *** 141,150 ****
> SELECT NOT ARRAY[1.1,1.2,1.3] = ARRAY[1.1,1.2,1.3] AS "FALSE";
> SELECT ARRAY[1,2] || 3 AS "{1,2,3}";
> SELECT 0 || ARRAY[1,2] AS "{0,1,2}";
> ! SELECT ARRAY[1,2] || ARRAY[3,4] AS "{{1,2},{3,4}}";
> SELECT ARRAY[[['hello','world']]] || ARRAY[[['happy','birthday']]] AS "ARRAY";
> SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}";
> ! SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{{0,0},{1,1},{2,2}}";
> SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}";
>
> -- array casts
> --- 141,150 ----
> SELECT NOT ARRAY[1.1,1.2,1.3] = ARRAY[1.1,1.2,1.3] AS "FALSE";
> SELECT ARRAY[1,2] || 3 AS "{1,2,3}";
> SELECT 0 || ARRAY[1,2] AS "{0,1,2}";
> ! SELECT ARRAY[1,2] || ARRAY[3,4] AS "{1,2,3,4}";
> SELECT ARRAY[[['hello','world']]] || ARRAY[[['happy','birthday']]] AS "ARRAY";
> SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}";
> ! SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{0,0,1,1,2,2}";
> SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}";
>
> -- array casts

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Fri, Aug 15, 2003 at 01:37:50PM -0500 I heard the voice of
Andrew L. Gould, and lo! it spake thus:
> On Friday 15 August 2003 01:13 pm, Ron Johnson wrote:
> >
> > Why are arrays even mentioned in the the same breath wrt relations
> > DBMSs? Aren't they an anathema to all we know and love?
>
> This gives rise to a couple of good questions:
>
> When and why would you want to use arrays instead of a relational model?

When it's appropriate 8-}

I've found it to be extremely rare, but it DOES happen. For instance, in
one project, I needed a bit of data in a record, which could store
between 1 and 7 (integer) values in it. Never less than 1, never more
than 7, and the ordering of them was essential to preserve. I *COULD* do
it in another table, with an order column (which would have to be
maintained 'manually' in the code), and, if one were neurotic, a seperate
constraint to keep from somehow getting too many records for each item in
the other table.... but it was far easier to just slap it in an array.

Now, any time ordering isn't important, OR a field can grow without
realistic bounds, arrays naturally aren't important. For instance, if
I'd needed 15 entries, I might well have gone ahead and referenced
another table, just because it would get too irritating dealing with the
arrays.

--
Matthew Fuller (MF4839) | fullermd(at)over-yonder(dot)net
Systems/Network Administrator | http://www.over-yonder.net/~fullermd/

"The only reason I'm burning my candle at both ends, is because I
haven't figured out how to light the middle yet"

Joe Conway <mail(at)joeconway(dot)com> writes:
> The attached patch fixes code and regression tests for the following
> 1) Array concatenation of equidimensional arrays:
> 2) Array literals or vars in ARRAY expressions:
> 3) Lower bound of outer array adjusted downward when an "element" (which
> could itself be an array) is concatenated onto the front of an array:

Applied with only marginal changes. (I thought the element-type checks
removed from parse_expr.c had better be applied at runtime; and
I noticed that array_cat scribbled on its inputs in some cases.)

> docs to follow once
> I'm sure what actually ends up being committed.

You're on the hook for docs fixes...

regards, tom lane

Tom Lane wrote:
> Applied with only marginal changes. (I thought the element-type checks
> removed from parse_expr.c had better be applied at runtime; and
> I noticed that array_cat scribbled on its inputs in some cases.)

Thanks!

> You're on the hook for docs fixes...
>

I'm on it.

Joe

Tom Lane wrote:
> You're on the hook for docs fixes...

Here's a patch for the documentation updates. Please apply.

Thanks,

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> Here's a patch for the documentation updates. Please apply.

Applied with one small change --- I thought the last example failed
to make the point that the elements of an ARRAY[] construct could be
dissimilar.

regards, tom lane

Tom applied this patch. Thanks.

---------------------------------------------------------------------------

Joe Conway wrote:
> Tom Lane wrote:
> > Could you look at how big a change it'd be, anyway? Offhand I think it
> > may just mean that the subscript-checking done in parse_expr.c needs to
> > be done at runtime instead. Remember parse_expr should only be
> > concerned about determining datatype, and for its purposes all arrays of
> > a given element type are the same --- subscript checking should happen
> > at runtime. (It seems likely that having an ndims field in ArrayExpr
> > is inappropriate.)
>
> The attached patch fixes code and regression tests for the following
> (docs to follow once applied):
>
> ========================================================================
> 1) Array concatenation of equidimensional arrays:
> ========================================================================
> regression=# select ARRAY[1,2] || ARRAY[3,4];
> ?column?
> -----------
> {1,2,3,4}
> (1 row)
>
> regression=# select ARRAY[[1],[2],[3]] || ARRAY[[4],[5]];
> ?column?
> -----------------------
> {{1},{2},{3},{4},{5}}
> (1 row)
>
> regression=# select ARRAY[[1,2],[2,3],[3,4]] || ARRAY[[4,5],[5,6]];
> ?column?
> ---------------------------------
> {{1,2},{2,3},{3,4},{4,5},{5,6}}
> (1 row)
>
>
> ========================================================================
> 2) Array literals or vars in ARRAY expressions:
> ========================================================================
> regression=# create table arr(f1 int[], f2 int[]);
> CREATE TABLE
> regression=# insert into arr values (ARRAY[[1,2],[3,4]],ARRAY[[5,6],[7,8]]);
> INSERT 2635544 1
> regression=# select ARRAY[f1,f2] from arr;
> array
> -------------------------------
> {{{1,2},{3,4}},{{5,6},{7,8}}}
> (1 row)
>
> regression=# select ARRAY['{{1,2},{3,4}}'::int[],'{{5,6},{7,8}}'::int[]];
> array
> -------------------------------
> {{{1,2},{3,4}},{{5,6},{7,8}}}
> (1 row)
>
>
> ========================================================================
> 3) Lower bound of outer array adjusted downward when an "element" (which
> could itself be an array) is concatenated onto the front of an array:
> ========================================================================
> regression=# create table arr(f1 int[]);
> CREATE TABLE
> regression=# insert into arr values ('{}');
> INSERT 2635538 1
> regression=# update arr set f1[-2] = 1;
> UPDATE 1
> regression=# select array_lower(f1,1) from arr;
> array_lower
> -------------
> -2
> (1 row)
>
> regression=# select array_lower(f1 || 2, 1) from arr;
> array_lower
> -------------
> -2
> (1 row)
>
> regression=# select array_lower(0 || f1, 1) from arr;
> array_lower
> -------------
> -3
> (1 row)
>
> regression=# update arr set f1 = ARRAY[[1,2],[3,4]];
> UPDATE 1
> regression=# select array_lower(f1,1) from arr;
> array_lower
> -------------
> 1
> (1 row)
>
> regression=# select array_lower(f1 || ARRAY[5,6], 1) from arr;
> array_lower
> -------------
> 1
> (1 row)
>
> regression=# select array_lower(ARRAY[-1,0] || f1, 1) from arr;
> array_lower
> -------------
> 0
> (1 row)
>
>
> Compiles without warnings and passes all regression tests. If there are
> no objections, please apply. As I mentioned above, docs to follow once
> I'm sure what actually ends up being committed.
>
> Joe

> Index: src/backend/executor/execQual.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/executor/execQual.c,v
> retrieving revision 1.141
> diff -c -r1.141 execQual.c
> *** src/backend/executor/execQual.c 8 Aug 2003 21:41:39 -0000 1.141
> --- src/backend/executor/execQual.c 15 Aug 2003 21:52:30 -0000
> ***************
> *** 1620,1635 ****
> ArrayType *result;
> List *element;
> Oid element_type = arrayExpr->element_typeid;
> ! int ndims = arrayExpr->ndims;
> int dims[MAXDIM];
> int lbs[MAXDIM];
>
> ! if (ndims == 1)
> {
> int nelems;
> Datum *dvalues;
> int i = 0;
>
> nelems = length(astate->elements);
>
> /* Shouldn't happen here, but if length is 0, return NULL */
> --- 1620,1637 ----
> ArrayType *result;
> List *element;
> Oid element_type = arrayExpr->element_typeid;
> ! int ndims = 0;
> int dims[MAXDIM];
> int lbs[MAXDIM];
>
> ! if (!arrayExpr->multidims)
> {
> + /* Elements are presumably of scalar type */
> int nelems;
> Datum *dvalues;
> int i = 0;
>
> + ndims = 1;
> nelems = length(astate->elements);
>
> /* Shouldn't happen here, but if length is 0, return NULL */
> ***************
> *** 1667,1672 ****
> --- 1669,1675 ----
> }
> else
> {
> + /* Must be nested array expressions */
> char *dat = NULL;
> Size ndatabytes = 0;
> int nbytes;
> ***************
> *** 1677,1688 ****
> bool firstone = true;
> int i;
>
> - if (ndims <= 0 || ndims > MAXDIM)
> - ereport(ERROR,
> - (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
> - errmsg("number of array dimensions exceeds the maximum allowed, %d",
> - MAXDIM)));
> -
> /* loop through and get data area from each element */
> foreach(element, astate->elements)
> {
> --- 1680,1685 ----
> ***************
> *** 1705,1714 ****
> --- 1702,1719 ----
> {
> /* Get sub-array details from first member */
> elem_ndims = ARR_NDIM(array);
> + ndims = elem_ndims + 1;
> + if (ndims <= 0 || ndims > MAXDIM)
> + ereport(ERROR,
> + (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
> + errmsg("number of array dimensions exceeds " \
> + "the maximum allowed, %d", MAXDIM)));
> +
> elem_dims = (int *) palloc(elem_ndims * sizeof(int));
> memcpy(elem_dims, ARR_DIMS(array), elem_ndims * sizeof(int));
> elem_lbs = (int *) palloc(elem_ndims * sizeof(int));
> memcpy(elem_lbs, ARR_LBOUND(array), elem_ndims * sizeof(int));
> +
> firstone = false;
> }
> else
> Index: src/backend/nodes/copyfuncs.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/nodes/copyfuncs.c,v
> retrieving revision 1.263
> diff -c -r1.263 copyfuncs.c
> *** src/backend/nodes/copyfuncs.c 8 Aug 2003 21:41:43 -0000 1.263
> --- src/backend/nodes/copyfuncs.c 15 Aug 2003 21:44:30 -0000
> ***************
> *** 947,953 ****
> COPY_SCALAR_FIELD(array_typeid);
> COPY_SCALAR_FIELD(element_typeid);
> COPY_NODE_FIELD(elements);
> ! COPY_SCALAR_FIELD(ndims);
>
> return newnode;
> }
> --- 947,953 ----
> COPY_SCALAR_FIELD(array_typeid);
> COPY_SCALAR_FIELD(element_typeid);
> COPY_NODE_FIELD(elements);
> ! COPY_SCALAR_FIELD(multidims);
>
> return newnode;
> }
> Index: src/backend/nodes/equalfuncs.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/nodes/equalfuncs.c,v
> retrieving revision 1.207
> diff -c -r1.207 equalfuncs.c
> *** src/backend/nodes/equalfuncs.c 8 Aug 2003 21:41:43 -0000 1.207
> --- src/backend/nodes/equalfuncs.c 15 Aug 2003 21:44:44 -0000
> ***************
> *** 409,415 ****
> COMPARE_SCALAR_FIELD(array_typeid);
> COMPARE_SCALAR_FIELD(element_typeid);
> COMPARE_NODE_FIELD(elements);
> ! COMPARE_SCALAR_FIELD(ndims);
>
> return true;
> }
> --- 409,415 ----
> COMPARE_SCALAR_FIELD(array_typeid);
> COMPARE_SCALAR_FIELD(element_typeid);
> COMPARE_NODE_FIELD(elements);
> ! COMPARE_SCALAR_FIELD(multidims);
>
> return true;
> }
> Index: src/backend/nodes/outfuncs.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/nodes/outfuncs.c,v
> retrieving revision 1.217
> diff -c -r1.217 outfuncs.c
> *** src/backend/nodes/outfuncs.c 8 Aug 2003 21:41:44 -0000 1.217
> --- src/backend/nodes/outfuncs.c 15 Aug 2003 21:45:34 -0000
> ***************
> *** 785,791 ****
> WRITE_OID_FIELD(array_typeid);
> WRITE_OID_FIELD(element_typeid);
> WRITE_NODE_FIELD(elements);
> ! WRITE_INT_FIELD(ndims);
> }
>
> static void
> --- 785,791 ----
> WRITE_OID_FIELD(array_typeid);
> WRITE_OID_FIELD(element_typeid);
> WRITE_NODE_FIELD(elements);
> ! WRITE_BOOL_FIELD(multidims);
> }
>
> static void
> Index: src/backend/nodes/readfuncs.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/nodes/readfuncs.c,v
> retrieving revision 1.161
> diff -c -r1.161 readfuncs.c
> *** src/backend/nodes/readfuncs.c 4 Aug 2003 02:39:59 -0000 1.161
> --- src/backend/nodes/readfuncs.c 15 Aug 2003 21:46:05 -0000
> ***************
> *** 659,665 ****
> READ_OID_FIELD(array_typeid);
> READ_OID_FIELD(element_typeid);
> READ_NODE_FIELD(elements);
> ! READ_INT_FIELD(ndims);
>
> READ_DONE();
> }
> --- 659,665 ----
> READ_OID_FIELD(array_typeid);
> READ_OID_FIELD(element_typeid);
> READ_NODE_FIELD(elements);
> ! READ_BOOL_FIELD(multidims);
>
> READ_DONE();
> }
> Index: src/backend/optimizer/util/clauses.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/optimizer/util/clauses.c,v
> retrieving revision 1.152
> diff -c -r1.152 clauses.c
> *** src/backend/optimizer/util/clauses.c 8 Aug 2003 21:41:55 -0000 1.152
> --- src/backend/optimizer/util/clauses.c 15 Aug 2003 21:48:03 -0000
> ***************
> *** 1515,1521 ****
> newarray->array_typeid = arrayexpr->array_typeid;
> newarray->element_typeid = arrayexpr->element_typeid;
> newarray->elements = FastListValue(&newelems);
> ! newarray->ndims = arrayexpr->ndims;
>
> if (all_const)
> return (Node *) evaluate_expr((Expr *) newarray,
> --- 1515,1521 ----
> newarray->array_typeid = arrayexpr->array_typeid;
> newarray->element_typeid = arrayexpr->element_typeid;
> newarray->elements = FastListValue(&newelems);
> ! newarray->multidims = arrayexpr->multidims;
>
> if (all_const)
> return (Node *) evaluate_expr((Expr *) newarray,
> Index: src/backend/parser/parse_expr.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_expr.c,v
> retrieving revision 1.160
> diff -c -r1.160 parse_expr.c
> *** src/backend/parser/parse_expr.c 4 Aug 2003 02:40:01 -0000 1.160
> --- src/backend/parser/parse_expr.c 15 Aug 2003 21:38:10 -0000
> ***************
> *** 748,754 ****
> List *element;
> Oid array_type;
> Oid element_type;
> - int ndims;
>
> /* Transform the element expressions */
> foreach(element, a->elements)
> --- 748,753 ----
> ***************
> *** 781,791 ****
> if (array_type != InvalidOid)
> {
> /* Elements are presumably of scalar type */
> ! ndims = 1;
> }
> else
> {
> /* Must be nested array expressions */
> array_type = element_type;
> element_type = get_element_type(array_type);
> if (!OidIsValid(element_type))
> --- 780,792 ----
> if (array_type != InvalidOid)
> {
> /* Elements are presumably of scalar type */
> ! newa->multidims = false;
> }
> else
> {
> /* Must be nested array expressions */
> + newa->multidims = true;
> +
> array_type = element_type;
> element_type = get_element_type(array_type);
> if (!OidIsValid(element_type))
> ***************
> *** 793,839 ****
> (errcode(ERRCODE_UNDEFINED_OBJECT),
> errmsg("could not find array type for datatype %s",
> format_type_be(array_type))));
> -
> - /*
> - * make sure the element expressions all have the same
> - * number of dimensions
> - */
> - ndims = 0;
> - foreach(element, newcoercedelems)
> - {
> - ArrayExpr *e = (ArrayExpr *) lfirst(element);
> -
> - if (!IsA(e, ArrayExpr))
> - ereport(ERROR,
> - (errcode(ERRCODE_SYNTAX_ERROR),
> - errmsg("multidimensional ARRAY[] must be built from nested array expressions")));
> - if (ndims == 0)
> - ndims = e->ndims;
> - else if (e->ndims != ndims)
> - ereport(ERROR,
> - (errcode(ERRCODE_SYNTAX_ERROR),
> - errmsg("nested array expressions must have common number of dimensions")));
> - if (e->element_typeid != element_type)
> - ereport(ERROR,
> - (errcode(ERRCODE_SYNTAX_ERROR),
> - errmsg("nested array expressions must have common element type")));
> -
> - }
> - /* increment the number of dimensions */
> - ndims++;
> -
> - /* make sure we don't have too many dimensions now */
> - if (ndims > MAXDIM)
> - ereport(ERROR,
> - (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
> - errmsg("number of array dimensions exceeds the maximum allowed, %d",
> - MAXDIM)));
> }
>
> newa->array_typeid = array_type;
> newa->element_typeid = element_type;
> newa->elements = newcoercedelems;
> - newa->ndims = ndims;
>
> result = (Node *) newa;
> break;
> --- 794,804 ----
> Index: src/backend/utils/adt/array_userfuncs.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/adt/array_userfuncs.c,v
> retrieving revision 1.7
> diff -c -r1.7 array_userfuncs.c
> *** src/backend/utils/adt/array_userfuncs.c 4 Aug 2003 00:43:25 -0000 1.7
> --- src/backend/utils/adt/array_userfuncs.c 15 Aug 2003 22:41:12 -0000
> ***************
> *** 132,138 ****
>
> /*-----------------------------------------------------------------------------
> * array_cat :
> ! * concatenate two nD arrays to form an (n+1)D array, or
> * push an (n-1)D array onto the end of an nD array
> *----------------------------------------------------------------------------
> */
> --- 132,138 ----
>
> /*-----------------------------------------------------------------------------
> * array_cat :
> ! * concatenate two nD arrays to form an nD array, or
> * push an (n-1)D array onto the end of an nD array
> *----------------------------------------------------------------------------
> */
> ***************
> *** 223,251 ****
> if (ndims1 == ndims2)
> {
> /*
> ! * resulting array has two element outer array made up of input
> ! * argument arrays
> */
> int i;
>
> ! ndims = ndims1 + 1;
> dims = (int *) palloc(ndims * sizeof(int));
> lbs = (int *) palloc(ndims * sizeof(int));
>
> ! dims[0] = 2; /* outer array made up of two input arrays */
> ! lbs[0] = 1; /* start lower bound at 1 */
>
> ! for (i = 0; i < ndims1; i++)
> {
> if (dims1[i] != dims2[i] || lbs1[i] != lbs2[i])
> ereport(ERROR,
> (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
> errmsg("cannot concatenate incompatible arrays"),
> ! errdetail("Arrays with differing dimensions are not "
> ! "compatible for concatenation.")));
>
> ! dims[i + 1] = dims1[i];
> ! lbs[i + 1] = lbs1[i];
> }
> }
> else if (ndims1 == ndims2 - 1)
> --- 223,251 ----
> if (ndims1 == ndims2)
> {
> /*
> ! * resulting array is made up of the elements (possibly arrays themselves)
> ! * of the input argument arrays
> */
> int i;
>
> ! ndims = ndims1;
> dims = (int *) palloc(ndims * sizeof(int));
> lbs = (int *) palloc(ndims * sizeof(int));
>
> ! dims[0] = dims1[0] + dims2[0];
> ! lbs[0] = lbs1[0];
>
> ! for (i = 1; i < ndims; i++)
> {
> if (dims1[i] != dims2[i] || lbs1[i] != lbs2[i])
> ereport(ERROR,
> (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
> errmsg("cannot concatenate incompatible arrays"),
> ! errdetail("Arrays with differing element dimensions are "
> ! "not compatible for concatenation.")));
>
> ! dims[i] = dims1[i];
> ! lbs[i] = lbs1[i];
> }
> }
> else if (ndims1 == ndims2 - 1)
> ***************
> *** 264,269 ****
> --- 264,272 ----
> /* increment number of elements in outer array */
> dims[0] += 1;
>
> + /* decrement outer array lower bound */
> + lbs[0] -= 1;
> +
> /* make sure the added element matches our existing elements */
> for (i = 0; i < ndims1; i++)
> {
> ***************
> *** 276,284 ****
> }
> }
> else
> - /* (ndims1 == ndims2 + 1) */
> {
> ! /*
> * resulting array has the first argument as the outer array, with
> * the second argument appended to the end of the outer dimension
> */
> --- 279,287 ----
> }
> }
> else
> {
> ! /* (ndims1 == ndims2 + 1)
> ! *
> * resulting array has the first argument as the outer array, with
> * the second argument appended to the end of the outer dimension
> */
> Index: src/include/nodes/primnodes.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/include/nodes/primnodes.h,v
> retrieving revision 1.91
> diff -c -r1.91 primnodes.h
> *** src/include/nodes/primnodes.h 11 Aug 2003 23:04:50 -0000 1.91
> --- src/include/nodes/primnodes.h 15 Aug 2003 21:37:35 -0000
> ***************
> *** 596,602 ****
> Oid array_typeid; /* type of expression result */
> Oid element_typeid; /* common type of expression elements */
> List *elements; /* the array elements */
> ! int ndims; /* number of array dimensions */
> } ArrayExpr;
>
> /*
> --- 596,602 ----
> Oid array_typeid; /* type of expression result */
> Oid element_typeid; /* common type of expression elements */
> List *elements; /* the array elements */
> ! bool multidims; /* true if elements are also ArrayExprs */
> } ArrayExpr;
>
> /*
> Index: src/test/regress/expected/arrays.out
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/arrays.out,v
> retrieving revision 1.17
> diff -c -r1.17 arrays.out
> *** src/test/regress/expected/arrays.out 21 Jul 2003 20:29:40 -0000 1.17
> --- src/test/regress/expected/arrays.out 15 Aug 2003 23:09:19 -0000
> ***************
> *** 190,199 ****
> {6,42}
> (1 row)
>
> ! SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{{1,2},{3,4}}";
> ! {{1,2},{3,4}}
> ! ---------------
> ! {{1,2},{3,4}}
> (1 row)
>
> SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}";
> --- 190,199 ----
> {6,42}
> (1 row)
>
> ! SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{1,2,3,4}";
> ! {1,2,3,4}
> ! -----------
> ! {1,2,3,4}
> (1 row)
>
> SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}";
> ***************
> *** 233,248 ****
> {0,1,2}
> (1 row)
>
> ! SELECT ARRAY[1,2] || ARRAY[3,4] AS "{{1,2},{3,4}}";
> ! {{1,2},{3,4}}
> ! ---------------
> ! {{1,2},{3,4}}
> (1 row)
>
> SELECT ARRAY[[['hello','world']]] || ARRAY[[['happy','birthday']]] AS "ARRAY";
> ! ARRAY
> ! ------------------------------------------
> ! {{{{hello,world}}},{{{happy,birthday}}}}
> (1 row)
>
> SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}";
> --- 233,248 ----
> {0,1,2}
> (1 row)
>
> ! SELECT ARRAY[1,2] || ARRAY[3,4] AS "{1,2,3,4}";
> ! {1,2,3,4}
> ! -----------
> ! {1,2,3,4}
> (1 row)
>
> SELECT ARRAY[[['hello','world']]] || ARRAY[[['happy','birthday']]] AS "ARRAY";
> ! ARRAY
> ! --------------------------------------
> ! {{{hello,world}},{{happy,birthday}}}
> (1 row)
>
> SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}";
> ***************
> *** 251,260 ****
> {{1,2},{3,4},{5,6}}
> (1 row)
>
> ! SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{{0,0},{1,1},{2,2}}";
> ! {{0,0},{1,1},{2,2}}
> ! ---------------------
> ! {{0,0},{1,1},{2,2}}
> (1 row)
>
> SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}";
> --- 251,260 ----
> {{1,2},{3,4},{5,6}}
> (1 row)
>
> ! SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{0,0,1,1,2,2}";
> ! {0,0,1,1,2,2}
> ! ---------------
> ! {0,0,1,1,2,2}
> (1 row)
>
> SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}";
> Index: src/test/regress/sql/arrays.sql
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/test/regress/sql/arrays.sql,v
> retrieving revision 1.14
> diff -c -r1.14 arrays.sql
> *** src/test/regress/sql/arrays.sql 29 Jun 2003 00:33:44 -0000 1.14
> --- src/test/regress/sql/arrays.sql 15 Aug 2003 23:02:06 -0000
> ***************
> *** 132,138 ****
> -- functions
> SELECT array_append(array[42], 6) AS "{42,6}";
> SELECT array_prepend(6, array[42]) AS "{6,42}";
> ! SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{{1,2},{3,4}}";
> SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}";
> SELECT array_cat(ARRAY[[3,4],[5,6]], ARRAY[1,2]) AS "{{3,4},{5,6},{1,2}}";
>
> --- 132,138 ----
> -- functions
> SELECT array_append(array[42], 6) AS "{42,6}";
> SELECT array_prepend(6, array[42]) AS "{6,42}";
> ! SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{1,2,3,4}";
> SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}";
> SELECT array_cat(ARRAY[[3,4],[5,6]], ARRAY[1,2]) AS "{{3,4},{5,6},{1,2}}";
>
> ***************
> *** 141,150 ****
> SELECT NOT ARRAY[1.1,1.2,1.3] = ARRAY[1.1,1.2,1.3] AS "FALSE";
> SELECT ARRAY[1,2] || 3 AS "{1,2,3}";
> SELECT 0 || ARRAY[1,2] AS "{0,1,2}";
> ! SELECT ARRAY[1,2] || ARRAY[3,4] AS "{{1,2},{3,4}}";
> SELECT ARRAY[[['hello','world']]] || ARRAY[[['happy','birthday']]] AS "ARRAY";
> SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}";
> ! SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{{0,0},{1,1},{2,2}}";
> SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}";
>
> -- array casts
> --- 141,150 ----
> SELECT NOT ARRAY[1.1,1.2,1.3] = ARRAY[1.1,1.2,1.3] AS "FALSE";
> SELECT ARRAY[1,2] || 3 AS "{1,2,3}";
> SELECT 0 || ARRAY[1,2] AS "{0,1,2}";
> ! SELECT ARRAY[1,2] || ARRAY[3,4] AS "{1,2,3,4}";
> SELECT ARRAY[[['hello','world']]] || ARRAY[[['happy','birthday']]] AS "ARRAY";
> SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}";
> ! SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{0,0,1,1,2,2}";
> SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}";
>
> -- array casts

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073