Re: 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

>-----Original Message-----
>From: pgsql-general-owner@postgresql.org
>[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Akbar
>Sent: donderdag 28 december 2006 13:10
>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
>