By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,660 Members | 1,299 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,660 IT Pros & Developers. It's quick & easy.

marking record origin in views

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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)

Nov 12 '05 #2

P: n/a
"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

Nov 12 '05 #3

P: n/a
"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

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.