Database Reference
In-Depth Information
To check whether the
INSERT
operation affects our view and relects the updated
tables, we will use a
SELECT
command as follows:
warehouse_db=# SELECT * FROM view_multiple_tables;
warehouse_name | year_created | city | amount | date
----------------+--------------+------------+--------+------------
Mark Corp | 2009 | New London | 1234 | 2014-07-10
| | | | 00:00:00
Bill & Co | 2014 | New London | 2345 | 2015-07-10
| | | | 00:00:00
(2 rows)
You can list your views as well that you just created by the
\dv
command in the
psql
utility as follows:
warehouse_db=# \dv
List of relations
Schema | Name | Type | Owner
--------+---------------------+------+----------
record | view_multiple_tables| view | postgres
record | view_warehouse_tbl | view | postgres
(2 rows)
Dropping the view is also a simple process like dropping a table. This can be done
using the following statement:
warehouse_db=# DROP VIEW view_warehouse_tbl;
You can see how easy it was to create views and empower your database design.
Materialized views
We said views are read only and the logical representation of data that is not stored
on disk or database. Views can be materialized in a way that they can still be a logical
representation of the data but when stored physically on the disk, at that moment
they become
materialized views
.
You will quickly jump to ask, "Why not stick to tables then?" Well, materialized
views have proved their capacity to work when performance is required, that is,
when there are more reads than writes. Materialized view data will be stored in a
table that can be indexed quickly when joining and that too has to be done when you
have to refresh the materialized views.
Thus, when your query has to bring millions of rows and querying the actual
database through views is expensive, you will deinitely like to have queries (views)
that are stored physically such as tables and give quick response. This is the reason
materialized views are used in data warehouses and Business Intelligence applications.
