join two tables without a key

On 03/04/2010 11:16, Dino Vliet wrote:

Hi postgresql list, If I have two tables with the same number of rows
but different columns and I want to create one table out of them what
would be the way to do that in postgresql?

Table A has N number of rows and columns X,Y,Z and Table B has N
number of rows and P,Q,R as columns. None of the tables have a
column which can be used as a key.

The resulting table should have N number of rows and X,Y,Z,P,Q,R as
columns.

How do the rows in the tables relate to each other? You need to decide
first how you match the rows in A and B.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 04/03/2010 11:16 AM, Dino Vliet wrote:

If I have two tables with the same number of rows but different columns and I want to create one table out of them what would be the way to do that in postgresql?

Table A has N number of rows and columns X,Y,Z and Table B has N number of rows and P,Q,R as columns. None of the tables have a column which can be used as a key.

The resulting table should have N number of rows and X,Y,Z,P,Q,R as columns.

You haven't said what you want the result to mean.

- Jeremy

--- On Sat, 4/3/10, Raymond O'Donnell <rod@iol.ie> wrote:

From: Raymond O'Donnell <rod@iol.ie>
Subject: Re: [GENERAL] join two tables without a key
To: "Dino Vliet" <dino_vliet@yahoo.com>
Cc: pgsql-general@postgresql.org
Date: Saturday, April 3, 2010, 1:01 PM

On 03/04/2010 11:16, Dino Vliet wrote:

Hi postgresql list, If I have two tables with the same number of rows
but different columns and I want to create one table out of them what
would be the way to do that in postgresql?

Table A has N number of rows and columns X,Y,Z and Table B has N
number of rows and P,Q,R as columns. None of the tables have a
column which can be used as a key.

The resulting table should have N number of rows and X,Y,Z,P,Q,R as
columns.

How do the rows in the tables relate to each other? You need to decide
first how you match the rows in A and B.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Hi Ray,

they don' t. It' s pure randomly generated data. 
Brgds

On 03/04/2010 12:32, Dino Vliet wrote:

--- On Sat, 4/3/10, Raymond O'Donnell <rod@iol.ie> wrote:
On 03/04/2010 11:16, Dino Vliet wrote:

Hi postgresql list, If I have two tables with the same number of rows
but different columns and I want to create one table out of them what
would be the way to do that in postgresql?

Table A has N number of rows and columns X,Y,Z and Table B has N
number of rows and P,Q,R as columns. None of the tables have a
column which can be used as a key.

The resulting table should have N number of rows and X,Y,Z,P,Q,R as
columns.

How do the rows in the tables relate to each other? You need to decide
first how you match the rows in A and B.

they don' t. It' s pure randomly generated data.

In that case, how about getting the cartesian product of the two tables,
and then LIMITing the result to N rows? - Something like this:

select
a.x, a.y, a.z,
b.p, b.q, b.r
from
a, b
limit N;

....substituting your value of N. It'll be slow if there are a lot of
rows in A and B, mind.

Ray.

Dino Vliet <dino_vliet@yahoo.com> wrote:

Hi postgresql list,

If I have two tables with the same number of rows but different columns and I
want to create one table out of them what would be the way to do that in
postgresql?

Table A has N number of rows and columns X,Y,Z and Table B has N number of rows
and P,Q,R as columns. None of the tables have a column which can be used as a
key.

The resulting table should have N number of rows and X,Y,Z,P,Q,R as columns.

Stupid table design, but okay:

test=# select * from a;
a1 | a2 | a3
-----+-----+-----
100 | 101 | 102
103 | 104 | 105
106 | 107 | 108
109 | 110 | 111
(4 Zeilen)

Zeit: 0,378 ms
test=*# select * from b;
b1 | b2 | b3
-----+-----+-----
201 | 202 | 203
204 | 205 | 206
207 | 208 | 209
210 | 211 | 212
(4 Zeilen)

Zeit: 0,317 ms
test=*# create sequence sa;
CREATE SEQUENCE
Zeit: 18,618 ms
test=*# create sequence sb;
CREATE SEQUENCE
Zeit: 0,939 ms
test=*# select foo_a.*, foo_b.* from (select nextval('sa') as id_a,*
from a) foo_a left join (select nextval('sb') as id_b,* from b) foo_b on
foo_a.id_A=foo_b.id_b;
id_a | a1 | a2 | a3 | id_b | b1 | b2 | b3
------+-----+-----+-----+------+-----+-----+-----
1 | 100 | 101 | 102 | 1 | 201 | 202 | 203
2 | 103 | 104 | 105 | 2 | 204 | 205 | 206
3 | 106 | 107 | 108 | 3 | 207 | 208 | 209
4 | 109 | 110 | 111 | 4 | 210 | 211 | 212
(4 Zeilen)

Zeit: 0,618 ms

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

Dino,

Not trying to be a "database purist" here, but...

If table A has no key, then why X,Y, and Z belong in one table?
And, table B has no key, then why P,Q, and R belong in one table?

And even more so, why are you trying to put X,Y,Z,P,Q,R into one table?
May be, if you tell us, what business entity/rule you are trying to
implement here, it'll be easier to help you.

Igor Neyman