Re: join two tables without a key

Dino Vliet <dino_vliet(at)yahoo(dot)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°