Thread: joining two simular (but not identical tables)

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.

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 |   | 67 |   | 8
(4 rows)

-- 
Michael Fuhr