Displaying two tables side by side

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

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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: Displaying two tables side by side

-----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 | 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

- - 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

iD8DBQFBGll0xJmyeGcXPhERApQ5AKCrOdLg4i6UpycLUGWxTLIpe68C6QCg k2UP
gcXbeO6VEw95obz1D8GQFQk=
=Ksq6
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Displaying two tables side by side

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 | 5
3 | 5 | 7 | 3
4 | 7 | 3 | 2
9 | 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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Displaying two tables side by side

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: Displaying two tables side by side

-----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

iD8DBQFBGn5f7UpzwH2SGd4RAjP2AKCZVDTMWX87VXI7SfpAyWsJ57NlygCg 6Ki9
5kOVpxAY5KPkHxpwpWFdEcY=
=O/Yc
-----END PGP SIGNATURE-----