marking record origin in views

[Claudio Lapidus]

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.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Peter Alberer]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

"Claudio Lapidus" <cl******@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Greg Stark]

"Peter Alberer" <h9******@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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html