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@xxxxxxxxxxxxxx >[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Akbar >Sent: donderdag 28 december 2006 13:10 >To: pgsql-general@xxxxxxxxxxxxxx >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 >
