pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedjoining two simular (but not identical tables)
2+ messages / 2 participants
[nested] [flat]
* joining two simular (but not identical tables)
@ 2005-09-04 12:19 Neil Dugan <postgres@butterflystitches.com.au>
2005-09-04 13:00 ` Re: joining two simular (but not identical tables) Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 2+ messages in thread
From: Neil Dugan @ 2005-09-04 12:19 UTC (permalink / raw)
To: pgsql-sql
I have two similar but not identical tables.
I would like to create a view that combines the contents of both tables
into a single view, where each record in each table is visible as a
separate record in the view.
table a
Column | Type | Modifiers
---------------+-----------------------+-----------
prcode | character varying(12) |
descr | character varying(55) |
rrp | numeric(10,5) |
sugcusmkdn | numeric(6,2) |
customerpr | numeric(7,2) |
costpr | numeric(11,6) |
lengths | character(1) |
profitpercent | numeric(6,2) |
table b
Column | Type | Modifiers
---------------+-----------------------+-----------
prcode | character varying(12) |
descr | character varying(55) |
rrp | numeric(10,5) |
customerpr | numeric(7,2) |
supdis | numeric(6,3) |
costpr | numeric(11,6) |
lengths | character(1) |
profitpercent | numeric(6,2) |
dnprice | numeric(7,2) |
stcode | character varying(18) |
dnprofit | numeric(5,2) |
Thanks for any help.
Regards Neil.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: joining two simular (but not identical tables)
2005-09-04 12:19 joining two simular (but not identical tables) Neil Dugan <postgres@butterflystitches.com.au>
@ 2005-09-04 13:00 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Fuhr @ 2005-09-04 13:00 UTC (permalink / raw)
To: Neil Dugan <postgres@butterflystitches.com.au>; +Cc: pgsql-sql
On Sun, Sep 04, 2005 at 10:19:12PM +1000, Neil Dugan wrote:
> I have two similar but not identical tables.
> I would like to create a view that combines the contents of both tables
> into a single view, where each record in each table is visible as a
> separate record in the view.
Sounds like you're looking for UNION.
http://www.postgresql.org/docs/8.0/static/queries-union.html
http://www.postgresql.org/docs/8.0/static/typeconv-union-case.html
Does the following example do what you want?
CREATE TABLE a (x integer, y integer);
INSERT INTO a (x, y) VALUES (1, 2);
INSERT INTO a (x, y) VALUES (3, 4);
CREATE TABLE b (x integer, z integer);
INSERT INTO b (x, z) VALUES (5, 6);
INSERT INTO b (x, z) VALUES (7, 8);
CREATE VIEW v AS
SELECT x, y, NULL AS z FROM a
UNION ALL
SELECT x, NULL AS y, z FROM b;
SELECT * FROM v;
x | y | z
---+---+---
1 | 2 |
3 | 4 |
5 | | 6
7 | | 8
(4 rows)
--
Michael Fuhr
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-09-04 13:00 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-04 12:19 joining two simular (but not identical tables) Neil Dugan <postgres@butterflystitches.com.au>
2005-09-04 13:00 ` Michael Fuhr <mike@fuhr.org>
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