Postgres Pro
Downloads
Home   >   mailing lists

Thread: select union with table name

select union with table name

From
Akbar
Date:
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?

Re: select union with table name

From
"Joris Dobbelsteen"
Date:
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
>

Re: select union with table name

From
Richard Broersma Jr
Date:
> 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.

Re: select union with table name

From
Reece Hart
Date:
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.

Re: select union with table name

From
Alban Hertroys
Date:
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 //