473,554 Members | 2,934 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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(ar ray[1,2,3], array[2,3]);
ERROR: function array_append(in teger[], integer[]) does not exist
regards
Pavel Stehule
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #1
20 4661
Pavel Stehule <st*****@kix.fs v.cvut.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 "concatenat ing" these arrays.
Joe, do we really have this implemented per spec?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #2
Joe Conway <ma**@joeconway .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 multidimensiona l 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #3
Tom Lane wrote:
Pavel Stehule <st*****@kix.fs v.cvut.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 "concatenat ing" 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

Nov 11 '05 #4
>
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 <st*****@kix.fs v.cvut.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 "concatenat ing" 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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #5
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #6

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,Yountvil le,CA,94599,5,0 62001,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 YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 11 '05 #7
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
multidimensiona l 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 multidimensiona l 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 ma*******@postg resql.org

Nov 11 '05 #8
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 multidimensiona l
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(ARR AY[-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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #9
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 multidimensiona l 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
multidimensiona l 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 multidimensiona l 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 ma*******@postg resql.org


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 11 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
2433
by: Jim Hefferon | last post by:
Hello, I'm getting an error join-ing strings and wonder if someone can explain why the function is behaving this way? If I .join in a string that contains a high character then I get an ascii codec decoding error. (The code below illustrates.) Why doesn't it just concatenate? I'm building up a web page by stuffing an array and then...
1
1851
by: Todd Anderson | last post by:
Dear Sirs and or Madaams, I need to write a file from @fields. I need the file to look like... $bgcolor = "black"; $textcolor = "black"; etc... or $bgcolor = "black"; $textcolor = "black"; @fields = split (/\|/, $line); foreach $field (@user_field_order) {
2
2075
by: Epson Barnett | last post by:
I'm working on learning .NET and I'm curious about the reason for using static methods in some cases. Specifically, the string class has a split and a join method, but only the join method is static. Both methods return a new string which is based on another string. It would seem that both should not be static. string mystring = "one,...
7
7387
by: !TG | last post by:
I recently learned of Join response.write join(ThisArray," ") I am having trouble with it, however and it appears to be caused by null characters. Is there anyway around this join problem with Arrays containing Null?
14
15011
by: Bob | last post by:
I have a function that takes in a list of IDs (hundreds) as input parameter and needs to pass the data to another step as a comma delimited string. The source can easily create this list of IDs in a comma-delimited string or string array. I don't want it to be a string because I want to overload this function, and it's sister already uses a...
2
1172
by: André | last post by:
Hi, I have a two-dimension array with integers and i want to join it into a string. See my code: dim va(10,5) as integer dim mystring as string .... for j=1 to 10 for k=1 to 5 mystring = Join(";",va(j,k).ToString)
9
1732
by: chadlupkes | last post by:
I'm getting NULLs where there shouldn't be. Any help is appreciated. Here are the tables: precinct Field Type Null Key Default Extra id smallint(6) PRI NULL auto_increment precinct_number int(11) 0 precinct_name varchar(20) MUL
23
19722
by: Summercool | last post by:
i think in Ruby, if you have an array (or list) of integers foo = you can use foo.join(",") to join them into a string "1,2,3" in Python... is the method to use ",".join() ? but then it must take a list of strings... not integers... any fast method?
6
1561
by: Matt Mackal | last post by:
I have an application that occassionally is called upon to process strings that are a substantial portion of the size of memory. For various reasons, the resultant strings must fit completely in RAM. Occassionally, I need to join some large strings to build some even larger strings. Unfortunately, there's no good way of doing this without...
2
7839
by: Bart | last post by:
Hi, i get the error "BC30518:Overload resolution failed because no accessible 'Join' can be called with these arguments" at line: hvd = Join(hvertp, ",") Any idea what's wrong here? Thanks bart
0
7578
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7497
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7780
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8010
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7862
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6119
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5136
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
1994
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
812
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.