In the view synopsis shown next, the CREATE keyword is used to create a view, while the REPLACE keyword is used to redefine the view if it already exists. The view attribute names can be given explicitly, or they can be inherited from the SELECT statement:

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

The following example shows how to create a view that lists only the user information without the password. This might be useful for implementing data authorization to restrict the applications for accessing the password. Note that the view column names are inherited from the SELECT list, as shown by the d in the account_information meta command:

CREATE VIEW account_information AS
SELECT
  account_id,
  first_name,
  last_name,
  email
FROM
  account;

car_portal=# d account_information
View "public.account_information"
   Column   |  Type   | Modifiers
------------+---------+-----------
 account_id | integer |
 first_name | text    |
 last_name  | text    |
 email      | text    |

The view account information column names can be assigned explicitly as shown in the following example. This might be useful when one needs to change the view column names:

CREATE VIEW account_information (account_id,first_name,last_name,email) AS
SELECT
  account_id,
  first_name,
  last_name,
 email
FROM
  account;

When replacing the view definition using the REPLACE keyword, the column list should be identical before and after the replacement, including the column type, name, and order. The following example shows what happens when trying to change the view column order:

car_portal=# CREATE OR REPLACE VIEW account_information AS
SELECT
account_id,
last_name,
first_name,
email
FROM
account;
ERROR:  cannot change name of view column "first_name" to "last_name"

Views in PostgreSQL can be categorized in one of the following categories on the basis of their usage:

Since recursion will be covered in the following chapters, we will focus here on the updatable and materialized views.

The materialized view synopsis differs a little bit from the normal view synopsis. Materialized views are a PostgreSQL extension, but several databases, such as Oracle, support it. As shown in the following synopsis below, a materialized view can be created in a certain TABLESPACE, which is logical since materialized views are physical objects:

CREATE MATERIALIZED VIEW table_name
    [ (column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

At the time of creation of a materialized view, it can be populated with data or left empty. If it is not populated, retrieving data from the unpopulated materialized view will raise an ERROR. The REFRESH MATERIALIZED VIEW statement can be used to populate a materialized view. The following example shows an attempt to retrieve data from an unpopulated materialized view:

car_portal=# CREATE MATERIALIZED VIEW test_mat_view AS SELECT 1 WITH NO DATA;
car_portal=# SELECT * FROM test_mat_view;
ERROR:  materialized view "test_mat_view" has not been populated
HINT:  Use the REFRESH MATERIALIZED VIEW command.
car_portal=# REFRESH MATERIALIZED VIEW test_mat_view;
REFRESH MATERIALIZED VIEW
car_portal=# SELECT * FROM test_mat_view;
 ?column?
----------
        1
(1 row)

Materialized views are often used with data warehousing. In data warehousing, several queries are required for business analyses and for decision support. The data in this kind of applications does not usually change, but the calculation and aggregation of this data is often a costly operation. In general, a materialized view can be used for the following:

Since materialized views are tables, they also can be indexed, leading to a great performance boost.

In our web car portal application, let us assume that we need to create a monthly summary report for the previous month to see which car was searched for the most. Let us also assume that the user search key has the following pattern:

Keyword1=value1&keyword2&value2& … & keywordn=valuen

For example, a search key could be :brand=opel&manufacturing_date=2013. The summary report can be created using the following query:

CREATE MATERIALIZED VIEW account_search_history AS
SELECT
  Key,
  count(*)
FROM
 (SELECT
    regexp_split_to_table(search_key, '&') AS key
 FROM
    account_history
  WHERE
    search_date >= date_trunc('month', now()-INTERVAL '1 month') AND
    search_date < date_trunc('month', now())) AS FOO
GROUP BY key;

car_portal=# SELECT * FROM account_search_history;
    key     | count
------------+-------
 brand=opel |     2
 model=2013 |     1
(2 rows)

The preceding query requires a lot of calculation because of the aggregation function, and also due to the big amount of data in the table. In our car web portal, if we have a million searches per month, that would mean a million records in the database. The query uses the following functions and predicates:

For completion purposes in our example, the generation of the materialized views should be automated using cron job in Linux or the job scheduler software. Additionally, the names of the generated materialized views should be suffixed with the month, since we need to generate a summary table for the search keys each month.

By default, simple PostgreSQL views are auto-updatable. Auto-updatable means that one could use the view with the DELETE, INSERT and UPDATE statements to manipulate the data of the underlying table. If the view is not updatable (which is not simple) due to the violation of one of the following constraints, the trigger and rule systems can be used to make it updatable. The view is automatically updatable if the following conditions are met:

The preceding conditions promise that the view attributes can be mapped directly to the underlying table attributes.

In the web car portal, let us assume that we have a view that shows only the accounts that are not seller accounts. This can be done as follows:

CREATE VIEW user_account AS
SELECT
account_id,
..first_name,
..last_name,
..email,
..password
FROM
..account
WHERE
..account_id NOT IN (
....SELECT account_id FROM seller_account);

The preceding view is updatable by default; thus, one can insert and update the view content as follows:

car_portal=# INSERT INTO user_account VALUES (default, 'test_first_name', 'test_last_name','[email protected]', 'password');
INSERT 0 1
car_portal=# DELETE FROM user_account WHERE first_name = 'test_first_name';
DELETE 1

In the case of an auto-updatable view, one cannot modify the base table if it does not contain data. For example, let us insert an account with a seller account, and then try to delete it:

car_portal=# WITH account_info AS (
INSERT INTO user_account
VALUES (default,'test_first_name','test_last_name','[email protected]','password')
RETURNING account_id)
INSERT INTO seller_account (account_id, street_name, street_number, zip_code, city) SELECT account_id, 'test_street', '555', '555', 'test_city' FROM account_info;
INSERT 0 1

car_portal=# DELETE FROM user_account WHERE first_name = 'test_first_name';
DELETE 0

In the preceding example, notice that the insert to the user account was successful, while the DELETE command did not delete any row.

If one is uncertain whether a view is auto updatable or not, he/she can verify this information using the information schema by checking the value of the is_insertable_into flag, as follows:

car_portal-# SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name = 'user_account';
  table_name  | is_insertable_into
--------------+--------------------
 user_account | YES
(1 row)