select union with table name

Try:
select blue.name, 'blue' from blue union select red.name, 'red' from
red;

Not tested, but that should work.
One thing to remember:
If blabla is in both blue and red, it will appear twice, instead of only
once as in your example.

- Joris

I want this:
name table_name
'blabla' blue
'bubu' blue
'haha' red
'kkk' red

Could I?

Here is an example from the table inheritance chapter:

SELECT p.relname, c.name, c.altitude
FROM cities c, pg_class p
WHERE c.altitude > 500 and c.tableoid = p.oid;

which returns:

relname | name | altitude
----------+-----------+----------
cities | Las Vegas | 2174
cities | Mariposa | 1953
capitals | Madison | 845

http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html

it should do what you want.

Regards,

Richard Broersma Jr.

On Thu, 2006-12-28 at 19:09 +0700, Akbar wrote:

select blue.name from blue union select red.name from red
give me this:
name
'blabla'
'bubu'
'haha'
'kkk'

I want this:
name table_name
'blabla' blue
'bubu' blue
'haha' red
'kkk' red

Could I?

select name,'blue' as "table_name" from blue union all select name,'red'
as "table_name" from red;

Note the 'all' after union... I suspect you'll want that or should at
least consider it.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush
kernel warning: universe consuming too many resources. Killing.
universe killed due to catastrophic leadership. Try -uspres carter.

Reece Hart wrote:

On Thu, 2006-12-28 at 19:09 +0700, Akbar wrote:
Note the 'all' after union... I suspect you'll want that or should at
least consider it.

Not using it will give the exact same results in a slower way; 'blue'
and 'red' are different, after all. You'll be hard pressed to find a
good excuse for not using UNION ALL here ;)

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //