How to Use the PostgreSQL Create View Statement
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 alphabet
— ERROR: 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