pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDisplaying two tables side by side
6+ messages / 5 participants
[nested] [flat]
* Displaying two tables side by side
@ 2004-08-11 14:11 David Garamond <lists@zara.6.isreserved.com>
2004-08-11 16:44 ` Re: Displaying two tables side by side Michael Kleiser <mkl@webde-ag.de>
2004-08-11 17:37 ` Re: Displaying two tables side by side Andreas Haumer <andreas@xss.co.at>
2004-08-11 18:32 ` Re: Displaying two tables side by side Rod Taylor <pg@rbt.ca>
0 siblings, 3 replies; 6+ messages in thread
From: David Garamond @ 2004-08-11 14:11 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Displaying two tables side by side
2004-08-11 14:11 Displaying two tables side by side David Garamond <lists@zara.6.isreserved.com>
@ 2004-08-11 16:44 ` Michael Kleiser <mkl@webde-ag.de>
2 siblings, 0 replies; 6+ messages in thread
From: Michael Kleiser @ 2004-08-11 16:44 UTC (permalink / raw)
To: David Garamond <lists@zara.6.isreserved.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Displaying two tables side by side
2004-08-11 14:11 Displaying two tables side by side David Garamond <lists@zara.6.isreserved.com>
@ 2004-08-11 17:37 ` Andreas Haumer <andreas@xss.co.at>
2004-08-11 18:55 ` Re: Displaying two tables side by side David Garamond <lists@zara.6.isreserved.com>
2004-08-11 20:15 ` Re: Displaying two tables side by side Gaetano Mendola <mendola@bigfoot.com>
2 siblings, 2 replies; 6+ messages in thread
From: Andreas Haumer @ 2004-08-11 17:37 UTC (permalink / raw)
To: David Garamond <lists@zara.6.isreserved.com>; +Cc: pgsql-sql
-----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
iD8DBQFBGll0xJmyeGcXPhERApQ5AKCrOdLg4i6UpycLUGWxTLIpe68C6QCgk2UP
gcXbeO6VEw95obz1D8GQFQk=
=Ksq6
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Displaying two tables side by side
2004-08-11 14:11 Displaying two tables side by side David Garamond <lists@zara.6.isreserved.com>
2004-08-11 17:37 ` Re: Displaying two tables side by side Andreas Haumer <andreas@xss.co.at>
@ 2004-08-11 18:55 ` David Garamond <lists@zara.6.isreserved.com>
1 sibling, 0 replies; 6+ messages in thread
From: David Garamond @ 2004-08-11 18:55 UTC (permalink / raw)
To: Andreas Haumer <andreas@xss.co.at>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Displaying two tables side by side
2004-08-11 14:11 Displaying two tables side by side David Garamond <lists@zara.6.isreserved.com>
2004-08-11 17:37 ` Re: Displaying two tables side by side Andreas Haumer <andreas@xss.co.at>
@ 2004-08-11 20:15 ` Gaetano Mendola <mendola@bigfoot.com>
1 sibling, 0 replies; 6+ messages in thread
From: Gaetano Mendola @ 2004-08-11 20:15 UTC (permalink / raw)
To: Andreas Haumer <andreas@xss.co.at>
-----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-----
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Displaying two tables side by side
2004-08-11 14:11 Displaying two tables side by side David Garamond <lists@zara.6.isreserved.com>
@ 2004-08-11 18:32 ` Rod Taylor <pg@rbt.ca>
2 siblings, 0 replies; 6+ messages in thread
From: Rod Taylor @ 2004-08-11 18:32 UTC (permalink / raw)
To: David Garamond <lists@zara.6.isreserved.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-08-11 20:15 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-11 14:11 Displaying two tables side by side David Garamond <lists@zara.6.isreserved.com>
2004-08-11 16:44 ` Michael Kleiser <mkl@webde-ag.de>
2004-08-11 17:37 ` Andreas Haumer <andreas@xss.co.at>
2004-08-11 18:55 ` David Garamond <lists@zara.6.isreserved.com>
2004-08-11 20:15 ` Gaetano Mendola <mendola@bigfoot.com>
2004-08-11 18:32 ` Rod Taylor <pg@rbt.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox