Thread: marking record origin in views

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 of
the particular record source. How can this be done?

thanks
cl.

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
>of
>the particular record source. How can this be done?

"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