marking record origin in views

Hmm, what about

create view v1 as
select *,1 as source from t1
union
select *,2 as source from t2;

Hello list

I have two tables with identical structure, one holds 'correct' data

(from

an application standpoint) and the other has data 'in error'. Anyway, I
need
sometimes to query both tables at the same time, so I constructed an
elementary view

create view v1 as select * from t1 union select * from t2;

But I would like to have an extra field (in the view) with the table

name

"Claudio Lapidus" <clapidus@hotmail.com> writes:

create view v1 as select * from t1 union select * from t2;
But I would like to have an extra field (in the view) with the table name of
the particular record source. How can this be done?

What's wrong with

create view v1 as
select *,'t1'::text as label from t1
union
select *,'t2'::text from t2;

Obviously, you can pick any field values and datatype you want.

Hint: use UNION ALL, not UNION.

regards, tom lane

"Peter Alberer" <h9351252@obelix.wu-wien.ac.at> writes:

Hmm, what about

create view v1 as
select *,1 as source from t1
union
select *,2 as source from t2;

And you might want "UNION ALL". Otherwise the database has to go eliminate
duplicate records (and there won't be any duplicate records with the "source"
column there anyways).

--
greg