A view in PostgreSQL
Tags: postgres, viewstatement, view, statement
Published: 2024-01-01 12:12:24

### A [view](https://www.postgresql.org/docs/current/sql-createview.html):

- is the named query based on a table.

- can only have [SELECT](https://www.postgresql.org/docs/current/sql-select.html) or [VALUES](https://www.postgresql.org/docs/current/sql-values.html) statement so it cannot have other statements like [INSERT](https://www.postgresql.org/docs/current/sql-insert.html), [UPDATE](https://www.postgresql.org/docs/current/sql-update.html), [DELETE](https://www.postgresql.org/docs/current/sql-delete.html), etc otherwise there is error.

- can have only single query.

- can be called with [FROM clause](https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-FROM) 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](https://www.postgresql.org/docs/current/sql-createview.html) explains a view.

For example, you create `person` table as shown below:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

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

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

```sql
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:

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

Then, `person` table is updated as shown below:

```sql
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:

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

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

```sql
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:

```sql
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](https://stackoverflow.com/questions/207964/column-specified-more-than-once-error-of-view-in-postgresql/77741015#77741015).
- You can set other type like `VARCHAR(20)` for `'Hello'`.

Then, calling `my_v` gets the result as shown below:

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

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

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

Then, calling `my_v` gets the result as shown below:

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