On Wed, Feb 27, 2008 at 07:39:51AM -0500, Kynn Jones wrote:
> Suppose I have two tables, A and B, with k(A) and k(B) columns respectively,
> and let's assume to begin with that they have the same number of rows r(A) =
> r(B) = r.
> What's the simplest way to produce a table C having r rows and k(A) + k(B)
> columns, and whose i-th row consists of the k(A) columns of the i-th row of
> A followed by the k(B) columns of the i-th row of B (for i = 1,...,r)? (By
> "i-th row of A" I mean the i-th row of the listing one would get from
> "SELECT * FROM A", and likewise for B.)
> The question could be generalized slightly to the case where the numbers of
> rows r(A) and r(B) are not equal. For example, if r(A) < r(B), the desired
> table C would have r(B) rows, and the first k(A) columns of its last r(B) -
> r(A) rows would be nulls, reminiscent of a table produced by a right outer
> join.
>
> Also, what's the technical term for this type of operation on two tables?
As Erik said, what you're doing doesn't sound like something you'd,
directly, ever want to do in a database---because relational algebra
doesn't have any implied ordering to rely on when doing the indexing, a
fact that Postgres and most databases exploit.
What you're doing sounds a bit like arrays containing some datatype, if
so why not express them (where said datatype is text) as:
CREATE TABLE a ( idx INTEGER PRIMARY KEY, value TEXT );
CREATE TABLE b ( idx INTEGER PRIMARY KEY, value TEXT );
"idx" being your "i" above. It's then trivial to do:
SELECT COALESCE(a.idx,b.idx) AS idx,
a.value AS a, b.value AS b
FROM a FULL OUTER JOIN b USING (idx);
to get all the values out.
Sam