## Re: How to "paste two tables side-by-side"?

From: Sam Mason pgsql-general(at)postgresql(dot)org Re: How to "paste two tables side-by-side"? 2008-02-28 11:35:59 20080228113559.GH1653@frubble.xen.chris-lamb.co.uk (view raw, whole thread or download thread mbox) 2008-02-27 12:39:51 from "Kynn Jones"  2008-02-27 13:22:24 from "Kynn Jones"   2008-02-27 21:37:00 from Erik Jones  2008-02-28 11:35:59 from Sam Mason pgsql-general
```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

```

### pgsql-general by date

 Next: From: Alban Hertroys Date: 2008-02-28 12:04:44 Subject: Re: Regarding interval conversion functions and a seeming lack of usefulness Previous: From: Albe Laurenz Date: 2008-02-28 11:28:04 Subject: Re: debug nonstandard use of \\ in a string literal