How to Use the PostgreSQL Create View Statement

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In PostgreSQL, a view can be defined as a virtual table that represents a stored query. Using a view can help simplify complex queries, because users can query the view with a relatively simple SELECT statement. Views are also useful because they can combine and simplify multiple tables into one virtual table. In this article, we’ll show you how to use the PostgreSQL CREATE VIEW statement to create a new view.

Prerequisites

In order to get the most out of this tutorial, you’ll need to have some working knowledge of SQL commands. You’ll also need to have PostgreSQL installed on your machine. You can check your PostgreSQL client server version by using the command psql -V. This will confirm that Postgres is installed and working properly.

Access the psql command-line console

The psql interactive terminal can be used to execute SQL commands in PostgreSQL. To access the psql command-line interface, first use the following command to log in as the postgres superuser:

1
sudo su - postgres

After entering the password for postgres. you can use the psql command to enter the interface.

The PostgreSQL CREATE VIEW statement

As we mentioned earlier, a VIEW is a virtual table in a PostgreSQL database that represents a stored object query. You can access data in a view using a SELECT statement and choosing what columns you want to display.

We use the CREATE VIEW statement to create a view in PostgreSQL.

The basic syntax for this statement is shown below:

1
2
3
4
CREATE [OR REPLACE] [TEMP | TEMPORARY]
VIEW view_name [ column_name ] AS
SELECT column1, column2..... FROM TABLE_NAME
WHERE [condition];

Let’s discuss each component of this syntax:

  • REPLACE – This optional syntax is used to replace the existing name of the view.

  • TEMPORARY – This optional syntax is used to create a temporary view.

  • SELECT – This statement represents the query on which the view is based.

PostgreSQL Create View Examples

Let’s look at an example of the PostgreSQL CREATE VIEW statement:

1
2
3
CREATE VIEW example
AS SELECT 'This is our First Example!'
AS first_example;

To display the created view, use the command SELECT * FROM example :

1
2
3
first_example
----------------------------
This is our First Example!

Create a Temporary View

In our next example, we’ll create a view that’s only temporary and will be removed when the session ends. You can use the following query to create a temporary view:

1
CREATE TEMP VIEW alphabet AS SELECT 'a' AS letter;

We can then use the SELECT statement again:

1
SELECT * FROM alphabet;

The output should look like this:

1
2
3
letter
--------
a

NOTE: If you end the session and start again, you’d get the following error when executing the CREATE VIEW TEMP on alphabetERROR: relation "alphabet" does not exist. This occurs because the relation is now removed on the database.

Use the PostgreSQL CREATE OR REPLACE VIEW

Let’s look at an example that uses the CREATE OR REPLACE VIEW statement:

1
2
CREATE OR REPLACE VIEW example AS
SELECT 'Replaced Example' AS first_example;

The output from this example will look like this:

1
2
3
4
first_example
------------------
Replaced Example
(1 ROW)

Create a PostgreSQL table

Views are created based on a query of one or more tables, so let’s review the process of creating a table in PostgreSQL.

The syntax for creating a table is shown below:

1
2
3
4
CREATE TABLE tbl_name(
col1 DATA_TYPE + CONSTRAINTS [OPTIONAL],
col2 DATA_TYPE + CONSTRAINTS [OPTIONAL]
);

We can then add some records using the INSERT statement:

1
2
INSERT INTO tbl_name(col1, col2)
VALUES (val1, val2);

To view a list of all tables that are available in our database, use the command shown below:

1
2
3
4
5
6
7
8
mydb=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | information | table | postgres
public | location | table | postgres
public | users | table | postgres
(3 rows)

Perform a PostgreSQL CREATE VIEW Example

Let’s check out another example that uses the CREATE VIEW statement:

1
2
CREATE VIEW person_info AS
SELECT id, name AS fullname FROM users;

We’ll use a SELECT statement to verify that our view is working correctly:

1
2
3
4
5
6
7
mydb=# SELECT * FROM person_info;
id | fullname
----+--------------
1 | James Ford
2 | Andrea Salve
3 | Miraj Straws
(3 ROWS)

Here’s another example:

1
CREATE VIEW person AS SELECT address, phone FROM location;

This time, the output will look like the following:

1
2
3
4
5
6
7
mydb=# SELECT * FROM person;
address | phone
-------------+--------------
Philippines | 09264678131
Australia | 0292787135
USA | 142554658792
(3 ROWS)

Conclusion

PostgreSQL views provide many key advantages over tables– they can represent just a subset of the total data contained in a table, they can join multiple tables to create a simpler virtual table, and they can simplify the complexity of your stored data. In this article, we showed you how to set up your own view using the PostgreSQL CREATE VIEW statement. With our step-by-step instructions, you’ll be able to create new views in your own PostgreSQL environment.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.