Querying from two tables as if they were appended

From: David Gaudine <davidg(at)alcor(dot)concordia(dot)ca>
To: pgsql-novice(at)postgresql(dot)org
Subject: Querying from two tables as if they were appended
Date: 2006-05-17 15:43:59
Message-ID: 446B44BF.4080109@alcor.concordia.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I have two tables with an identical layout. The difference is that one
has data from this year and one has data from last year. For a
simplified example let's call them "table05" and "table06", and say that
each has a single column called "name". So we have

table05:
David
Bob

table06:
David
John
Paul

I want to select all rows from both tables, so the result (order not
important) is
David
Bob
David
John
Paul

This is surely trivial, but I read the section about joined tables and
it shows only how to give a result that has 2 columns, like

test=# select * from table05,table06;
name | name
-------+-------
David | David
David | John
David | Paul
Bob | David
Bob | John
Bob | Paul

One solution would to to create a third table and copy the first two
into it, but since the two tables change frequently this is
undesirable. How can I do it by querying the existing tables?

Note: This example is oversimplified. The real query will also test
other columns, like for example
select * from ( however I do the above) where age > 25
so the number of rows in the result will not be the sum of the number of
rows in each table.

David

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message John Ensign 2006-05-17 15:53:32 dates
Previous Message Q Beukes 2006-05-17 12:21:47 OIDS