Re: [GENERAL] join two tables without a key
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 > -Original Message- > From: Dino Vliet [mailto:dino_vl...@yahoo.com] > Sent: Saturday, April 03, 2010 7:32 AM > To: r...@iol.ie > Cc: pgsql-general@postgresql.org > Subject: Re: join two tables without a key > > --- On Sat, 4/3/10, Raymond O'Donnell wrote: > > > > From: Raymond O'Donnell > Subject: Re: [GENERAL] join two tables without a key > To: "Dino Vliet" > 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 > r...@iol.ie > > > > Hi Ray, > > > > > > > > they don' t. It' s pure randomly generated data. > > > > > Brgds > > > > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] join two tables without a key
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.
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°
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] join two tables without a key
On 03/04/2010 12:32, Dino Vliet wrote: > --- On Sat, 4/3/10, Raymond O'Donnell 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] join two tables without a key
--- On Sat, 4/3/10, Raymond O'Donnell wrote: From: Raymond O'Donnell Subject: Re: [GENERAL] join two tables without a key To: "Dino Vliet" 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 r...@iol.ie Hi Ray, they don' t. It' s pure randomly generated data. Brgds
Re: [GENERAL] join two tables without a key
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
