postgresql create view and save table name in new colum

Question

I´m just starting with views in Postgresql 9.1 and have following question.

Given are the following tables:

pupils

name  |  age
============
john     15
jack     16

cars

type | owner
============
volvo   1
vw      2

Is it possible to create a view that gives me this as result

ident | column
==============
john   pupils
jack   pupils
volvo  cars
vw     cars

My example might look a bit abstract but I´m in the the need to create one view from very different tables which all share one column which I´m interested in but except this have nothing in common.

My poor first step:

CREATE OR REPLACE VIEW test AS 
SELECT pupils.name, cars.type AS ident
FROM pupils,cats

thanks,

t book

score 2 · Accepted Answer · 2014-04-02 10:22:02Z

2

You don't want a cartesian join between the two tables, you want a UNION

create or replace view test 
as
select name     as ident, 
       'pupils' as table_source
from pupils
union all
select type, 
       'cars'
FROM cars
union all
select cloud_number, 
       'clouds'
FROM clouds
select tree_name, 
       'trees'
FROM trees;

You can add any number of tables to this. The only restriction is that the common column must have a "compatible" data type (e.g. all varchar). If e.g. the 5th table has a date column that you want to include you need to add an explicit type case (or use a formatting function).

The column names of the result are determined by the column names of the first select in the union.

Alternatively you could also name them in the create view part

create or replace view (ident, some_column) test 
as
select ...

edited Apr 2, 2014 at 10:22

answered Apr 2, 2014 at 10:03

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Anatol Over a year ago

create thank you,this works. But how could I expand the select from pupils for more tables lets also a union with tables pupils,trees,clouds and cars?

user330315 Over a year ago

@tBook Just add more union all branches. That isn't limited to two tables.

Collectives™ on Stack Overflow

postgresql create view and save table name in new colum

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related