Skip site navigation (1) Skip section navigation (2)

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

From: Sam Mason <sam(at)samason(dot)me(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: How to "paste two tables side-by-side"?
Date: 2008-02-28 11:35:59
Message-ID: 20080228113559.GH1653@frubble.xen.chris-lamb.co.uk (view raw or flat)
Thread:
Lists: 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

In response to

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group