FW: select union with table name - Mailing list pgsql-general

Try this

Select name, 'blue' as table_name from blue
Union all
Select name, 'red' as table_name from red

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Akbar
Sent: Thursday, December 28, 2006 2:10 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] select union with table name

Hi, I have two tables.
create table blue (
  id serial primary key,
  name text not null,
  kill text not null
);

create table red (
  id serial primary key,
  name text not null,
  kiss text not null
);

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?

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq