A view in PostgreSQL

Buy Me a Coffee☕

A view:

• is the named query based on a table.

• can only have SELECT or VALUES statement so it cannot have other statements like INSERT, UPDATE, DELETE, etc otherwise there is error.

• can have only single query.

• can be called with FROM clause of SELECT statement.

• can be used with INSERT, UPDATE or DELETE statement to change the contents of its base table.

• cannot have zero or multiple queries otherwise there is error.

*The doc explains a view.

For example, you create person table as shown below:

CREATE TABLE person (
  id INT,
  first_name VARCHAR(20),
  last_name VARCHAR(20),
  age INT
);

Then, you insert 2 rows into person table as shown below:

INSERT INTO person (id, first_name, last_name, age) 
VALUES (1, 'John', 'Smith', 27), (2, 'David', 'Miller', 32);

Now, you can create my_v view with SELECT statement as shown below:

CREATE VIEW my_v AS
  SELECT first_name, age FROM person;

Then, you can call my_v with FROM clause of SELECT statement as shown below. *id and last_name are not usable:

postgres=# SELECT * FROM my_v;
 first_name | age
------------+-----
 John       |  27
 David      |  32
(2 rows)

And, you can insert 2 rows to person table with my_v as shown below. *id and last_name are not usable:

INSERT INTO my_v (first_name, age) 
VALUES ('Robert', 18), ('Mark', 40);

Then, 2 rows are inserted to person table as shown below:

postgres=# SELECT * FROM person;
 id | first_name | last_name | age
----+------------+-----------+-----
  1 | John       | Smith     |  27
  2 | David      | Miller    |  32
    | Robert     |           |  18
    | Mark       |           |  40
(4 rows)

And, you can update person table with my_v as shown below. *id and last_name are not usable:

UPDATE my_v SET first_name = 'Tom' WHERE age = 32;

Then, person table is updated as shown below:

postgres=# SELECT * FROM person;
 id | first_name | last_name | age
----+------------+-----------+-----
  1 | John       | Smith     |  27
    | Robert     |           |  18
    | Mark       |           |  40
  2 | Tom        | Miller    |  32
(4 rows)

And, you can delete 2 rows from person table with my_v as shown below. *id and last_name are not usable:

DELETE FROM my_v WHERE age = 18 OR age = 40;

Then, 2 rows are deleted from person table as shown below:

postgres=# SELECT * FROM person;
 id | first_name | last_name | age
----+------------+-----------+-----
  1 | John       | Smith     |  27
  2 | Tom        | Miller    |  32
(2 rows)

And, you can create my_v view with raw values as shown below:

CREATE VIEW my_v AS
  SELECT TEXT 'Hello', 'World';

*Memos:

• TEST can set the type and column name for 'Hello'.
• Unsetting TEST gets the error.
• You can set other type like VARCHAR(20) for 'Hello'.

Then, calling my_v gets the result as shown below:

postgres=# SELECT * FROM my_v;
 text  | ?column?
-------+----------
 Hello | World
(1 row)

And, you can create my_v view with VALUES statement as shown below:

CREATE VIEW my_v AS
  VALUES ('Hello', 'World');

Then, calling my_v gets the result as shown below:

postgres=# SELECT * FROM my_v;
 column1 | column2
---------+---------
 Hello   | World
(1 row)