Postgres Pro
Downloads
Home   >   mailing lists

Thread: Database Views

Database Views

From
"gcj"
Date:

Hi,

 

I’m a java developer and a novice Postgres user.  I’m trying to create a view that will display common fields from several different tables to simplify some JDBC queries.

 

To illustrate my question, consider this example -- I have the following three tables:

 

CARS

TRUCKS

SUVS

 

Although the tables contain numerous columns each, they each contain the following columns in common:

 

Manufacturer

Identification Number

Color

 

I would like to create a view that will display this common information as well as a column called “Type” that will indicate what kind of record it is – eg Car, Truck, or SUV.

 

I’m not trying to create a view that merges multiple rows into a single row in the view, but instead a view that will display a subset of each row from the different tables.  For example, if each of the individual tables contains 100 rows, the view would contain 300 rows.

 

I have no problem creating a view that queries a single table, but I get stuck when trying to pull multiple tables into the view.

 

Can this type of view be created?  If so, can someone point me in the right direction – a keyword or syntax example would be appreciated.

 

Thanks,

 

gcj

 

Re: Database Views

From
Frank Bax
Date:
gcj wrote:
> I have the following three tables:  CARS  TRUCKS  SUVS
  >
> Although the tables contain numerous columns each, they each contain the
> following columns in common:
>
> Manufacturer
> Identification Number
> Color
>
> I would like to create a view that will display this common information
> as well as a column called “Type” that will indicate what kind of record
> it is – eg Car, Truck, or SUV.


create table car (manufacturer varchar, vin varchar, color varchar);
create table truck (manufacturer varchar, vin varchar, color varchar);
create table suv (manufacturer varchar, vin varchar, color varchar);

insert into car values('Toyota','TTT', 'Silver');
insert into truck values('Mack','QQQ', 'Black');
insert into suv values('Lexxus','ZZZ', 'Yellow');

create or replace view vehicle as
select 'car' as type,manufacturer,vin,color from car
union
select 'truck' as type,manufacturer,vin,color from truck
union
select 'suv' as type,manufacturer,vin,color from suv
;

select * from vehicle;

  type  | manufacturer | vin | color
-------+--------------+-----+--------
  car   | Toyota       | TTT | Silver
  suv   | Lexxus       | ZZZ | Yellow
  truck | Mack         | QQQ | Black

Re: Database Views

From
Tom Lane
Date:
Frank Bax <fbax@sympatico.ca> writes:
> create or replace view vehicle as
> select 'car' as type,manufacturer,vin,color from car
> union
> select 'truck' as type,manufacturer,vin,color from truck
> union
> select 'suv' as type,manufacturer,vin,color from suv
> ;

UNION ALL would be better, since it would stop the system from uselessly
trying to eliminate duplicate rows ...

            regards, tom lane