Postgres Pro
Downloads
Home   >   mailing lists

Thread: Displaying two tables side by side

Displaying two tables side by side

From
David Garamond
Date:
How can you display two tables side by side? Example:
> select * from t1; a | b
---+--- 2 | 2 3 | 5 4 | 7 9 | 0
> select * from t2; c | d
---+--- 4 | 5 7 | 3 3 | 2 1 | 1 2 | 0

Intended output: a | b | c | d
---+---+---+--- 2 | 2 | 4 | 5 3 | 5 | 7 | 3 4 | 7 | 3 | 2 9 | 0 | 1 | 1   |   | 2 | 0

Each table has no keys (and no OIDs). Order is not important, but each 
row from each table needs to be displayed exactly once.

--
dave

Re: Displaying two tables side by side

From
Andreas Haumer
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi!

David Garamond wrote:
> How can you display two tables side by side? Example:
>
>> select * from t1;
>  a | b
> ---+---
>  2 | 2
>  3 | 5
>  4 | 7
>  9 | 0
>
>> select * from t2;
>  c | d
> ---+---
>  4 | 5
>  7 | 3
>  3 | 2
>  1 | 1
>  2 | 0
>
> Intended output:
>  a | b | c | d
> ---+---+---+---
>  2 | 2 | 4 | 5
>  3 | 5 | 7 | 3
>  4 | 7 | 3 | 2
>  9 | 0 | 1 | 1
>    |   | 2 | 0
>
> Each table has no keys (and no OIDs). Order is not important, but each
> row from each table needs to be displayed exactly once.
>
You could try to use PosgreSQL's ctid system column to join on like this:

test=# select *,ctid from t1;a | b | ctid
- ---+---+-------2 | 2 | (0,1)3 | 5 | (0,2)4 | 7 | (0,3)9 | 0 | (0,4)


test=# select *,ctid from t2;c | d | ctid
- ---+---+-------4 | 5 | (0,1)7 | 3 | (0,2)3 | 2 | (0,3)1 | 1 | (0,4)2 | 0 | (0,5)


test=# select * from t1 right outer join t2 on (t1.ctid=t2.ctid);a | b | c | d
- ---+---+---+---2 | 2 | 4 | 53 | 5 | 7 | 34 | 7 | 3 | 29 | 0 | 1 | 1  |   | 2 | 0


Note that this is of course very platform specific. On Oracle
you could use rownum, for example.
I don't have a more portable solution on hand right now.

HTH

- - andreas

- --
Andreas Haumer                     | mailto:andreas@xss.co.at
*x Software + Systeme              | http://www.xss.co.at/
Karmarschgasse 51/2/20             | Tel: +43-1-6060114-0
A-1100 Vienna, Austria             | Fax: +43-1-6060114-71
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFBGll0xJmyeGcXPhERApQ5AKCrOdLg4i6UpycLUGWxTLIpe68C6QCgk2UP
gcXbeO6VEw95obz1D8GQFQk=
=Ksq6
-----END PGP SIGNATURE-----

Re: Displaying two tables side by side

From
Michael Kleiser
Date:
select  ( select a from t1 where CAST(t1.oid AS int) - CAST( (select min(oid) from t1) AS int ) = t_all.rownum ) AS a
, ( select b from t1 where CAST(t1.oid AS int) - CAST( (select min(oid) from t1) AS int ) = t_all.rownum ) AS a
, ( select c from t2 where CAST(t2.oid AS int) - CAST( (select min(oid) from t2) AS int ) = t_all.rownum ) AS a
, ( select d from t2 where CAST(t2.oid AS int) - CAST( (select min(oid) from t2) AS int ) = t_all.rownum ) AS a
from (  select cast(t1.oid AS int) - CAST( (select min(oid) from t1) AS int ) AS rownum    UNION  select cast(t2.oid AS
int)- CAST( (select min(oid) from t2) AS int ) AS rownum
 
) AS t_all;
 a | a | a | a
---+---+---+--- 2 | 2 | 4 | 5 3 | 5 | 7 | 3 4 | 7 | 3 | 2 9 | 0 | 1 | 1   |   | 2 | 0


David Garamond schrieb:
> How can you display two tables side by side? Example:
> 
>  > select * from t1;
>  a | b
> ---+---
>  2 | 2
>  3 | 5
>  4 | 7
>  9 | 0
> 
>  > select * from t2;
>  c | d
> ---+---
>  4 | 5
>  7 | 3
>  3 | 2
>  1 | 1
>  2 | 0
> 
> Intended output:
>  a | b | c | d
> ---+---+---+---
>  2 | 2 | 4 | 5
>  3 | 5 | 7 | 3
>  4 | 7 | 3 | 2
>  9 | 0 | 1 | 1
>    |   | 2 | 0
> 
> Each table has no keys (and no OIDs). Order is not important, but each 
> row from each table needs to be displayed exactly once.
> 
> -- 
> dave
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: Displaying two tables side by side

From
Rod Taylor
Date:
Depending on the size of your structures, something like the below may
be significantly faster than the subselect alternative, and more
reliable than the ctid alternative.

CREATE TYPE result_info AS (a integer, b integer, c integer, d integer);


CREATE OR REPLACE FUNCTION parallelselect() RETURNS SETOF result_info AS
'
DECLARE res result_info%rowtype;
 ct1_found boolean DEFAULT true; ct2_found boolean DEFAULT true;
 ct1 CURSOR FOR SELECT a,b FROM t1; ct2 CURSOR FOR SELECT c,d FROM t2;

BEGIN
 OPEN ct1; OPEN ct2;
 LOOP
   FETCH ct1 INTO res.a, res.b;   ct1_found := FOUND;
   FETCH ct2 INTO res.c, res.d;   ct2_found := FOUND;
   IF ct1_found OR ct2_found THEN     RETURN NEXT res;   ELSE      EXIT;   END IF;
 END LOOP;
 RETURN;
END;
' LANGUAGE plpgsql;

SELECT * FROM parallelselect() AS tab;a | b | c | d
---+---+---+---2 | 2 | 4 | 53 | 5 | 7 | 34 | 7 | 3 | 29 | 0 | 1 | 1  |   | 2 | 0
(5 rows)


On Wed, 2004-08-11 at 10:11, David Garamond wrote:
> How can you display two tables side by side? Example:
> 
>  > select * from t1;
>   a | b
> ---+---
>   2 | 2
>   3 | 5
>   4 | 7
>   9 | 0
> 
>  > select * from t2;
>   c | d
> ---+---
>   4 | 5
>   7 | 3
>   3 | 2
>   1 | 1
>   2 | 0
> 
> Intended output:
>   a | b | c | d
> ---+---+---+---
>   2 | 2 | 4 | 5
>   3 | 5 | 7 | 3
>   4 | 7 | 3 | 2
>   9 | 0 | 1 | 1
>     |   | 2 | 0
> 
> Each table has no keys (and no OIDs). Order is not important, but each 
> row from each table needs to be displayed exactly once.
> 
> --
> dave
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: Displaying two tables side by side

From
David Garamond
Date:
Andreas Haumer wrote:
> You could try to use PosgreSQL's ctid system column to join on like this:
> 
> test=# select *,ctid from t1;
>  a | b | ctid
> - ---+---+-------
>  2 | 2 | (0,1)
>  3 | 5 | (0,2)
>  4 | 7 | (0,3)
>  9 | 0 | (0,4)
> 
> 
> test=# select *,ctid from t2;
>  c | d | ctid
> - ---+---+-------
>  4 | 5 | (0,1)
>  7 | 3 | (0,2)
>  3 | 2 | (0,3)
>  1 | 1 | (0,4)
>  2 | 0 | (0,5)
> 
> 
> test=# select * from t1 right outer join t2 on (t1.ctid=t2.ctid);
>  a | b | c | d
> - ---+---+---+---
>  2 | 2 | 4 | 5
>  3 | 5 | 7 | 3
>  4 | 7 | 3 | 2
>  9 | 0 | 1 | 1
>    |   | 2 | 0
> 
> 
> Note that this is of course very platform specific. On Oracle
> you could use rownum, for example.
> I don't have a more portable solution on hand right now.
> 
> HTH

Thanks, I just found out about ctid. I was thinking on a rownum 
equivalent too, actually.

I guess a more portable solution would be creating a temporary table for 
each table to add the ctid/"row counter" equivalent, and then join on that.

-- 
dave

Re: Displaying two tables side by side

From
Gaetano Mendola
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Andreas Haumer wrote:

| test=# select * from t1 right outer join t2 on (t1.ctid=t2.ctid);

"full outer join"  is better in this case.




Regards
Gaetano Mendola


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBGn5f7UpzwH2SGd4RAjP2AKCZVDTMWX87VXI7SfpAyWsJ57NlygCg6Ki9
5kOVpxAY5KPkHxpwpWFdEcY=
=O/Yc
-----END PGP SIGNATURE-----