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

On Feb 27, 2008, at 7:22 AM, Kynn Jones wrote:

> On Wed, Feb 27, 2008 at 7:39 AM, Kynn Jones <kynnjo@gmail.com> 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.)
>
> Expanding on my own post here, it occurred to me that it would be
> very nice to have a function (say) index, that, when used in a
> SELECT list, would yield the position in the corresponding table of
> the current row.  E.g. the expression
>
>   SELECT index(*) FROM A;
>
> would produce the same table as
>
>   SELECT generate_series( 1, r(A) );
>
> It would also be useful to have a "subscripting function" s (which
> may be regarded as somewhat of the inverse of index()) that, given
> a table expression E, and an "index expression" I (which could be a
> single index or range, or a list of such), will return the table
> consisting of the rows in E designated by the indices in I).  For
> example, either one these queries
>
>   SELECT s( A, 1, 2, 3 );
>   SELECT * FROM s( A, 1, 2, 3 );
>
> would produce the same table as
>
>   SELECT * FROM A LIMIT 3;
>
> Does anything like index() or s() exist?  If not, are there other
> functions that may be useful in an implementation of index() or s()?

If I'm understanding what you're asking for here, no it doesn't and
it doesn't make sense for it to.  Table rows are inherently
unordered, to get an ordering you supply and ORDER BY clause.  To get
a certain position in that ordering you take on an OFFSET and/or
LIMIT clause.

Another way of putting it is that at the base table level, rows do
not have position within the table.  As soon as you update a row, it
"moves" as the original row is deleted and a new one is inserted.
Once vacuum does it's job, then newly inserted rows are inserted over
previously deleted rows, i.e in the "middle" of the table.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

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