Postgres Problem

[lazydog]

Gday,

Just wondering if u guys ever had this problem when extracting data from a postgres database, so u dont have to worry bout re formulating and mereging the resultset in php.

I have 2 tables

table name test2
id | name | descr
----+---------+-------
2 | test2 | test2_desc

table name test1
id | name | descr
----+--------+-------
1 | test1 | test1_desc

Now test 1 and test 2 both inherit their ids.

Is there a way to select * from both test1 and test2 with all the records in the resultset being in seperate entries? ie
id | name | descr
----+--------+-------
1 | test1 | test1_desc
2 | test2 | test2_desc

And not

id | name | descr | id | name | descr
----+---------+-------+----+--------+-------
1 | test1 | test1_desc | 2 | test2 | test2_desc

?

Any light would be much appreciated...

Cheers

[zhabala]

Usually it could be done with UNION clause for SELECT statement, but I don't know whether it is implemented in Postgres. Try

SELECT FROM table1 UNION SELECT FROM table2;

Zdenek

[Sxooter]

Actually, there's precious little SQL spec that isn't implemented in postgresql, mostly having to do with nested transactions and such.

Unions were implemented many many moons ago in Postgresql, so this should work fine.

Note you can make a view out of it if you want:

create view biggy as select from test1 union select from test2;
select * from biggy where id>4 and id<100;

[lazydog]

Thanks for the suggestions guys,

This is how u do it if ur wondering..

SELECT from test1 UNION ALL SELECT from test2;

Cheers