How to Use the PostgreSQL Create View Statement
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.
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
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
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:
CREATE [OR REPLACE] [TEMP | TEMPORARY]
VIEW view_name [ column_name ] AS
SELECT column1, column2..... FROM TABLE_NAME
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:
CREATE VIEW example
AS SELECT 'This is our First Example!'
To display the created view, use the command
SELECT * FROM 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:
CREATE TEMP VIEW alphabet AS SELECT 'a' AS letter;
We can then use the
SELECT statement again:
SELECT * FROM alphabet;
The output should look like this:
NOTE: If you end the session and start again, you’d get the following error when executing the
CREATE VIEW TEMP on
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:
CREATE OR REPLACE VIEW example AS
SELECT 'Replaced Example' AS first_example;
The output from this example will look like this:
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:
CREATE TABLE tbl_name(
col1 DATA_TYPE + CONSTRAINTS [OPTIONAL],
col2 DATA_TYPE + CONSTRAINTS [OPTIONAL]
We can then add some records using the
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:
List of relations
Schema | Name | Type | Owner
public | information | table | postgres
public | location | table | postgres
public | users | table | postgres
Perform a PostgreSQL CREATE VIEW Example
Let’s check out another example that uses the
CREATE VIEW statement:
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:
mydb=# SELECT * FROM person_info;
id | fullname
1 | James Ford
2 | Andrea Salve
3 | Miraj Straws
Here’s another example:
CREATE VIEW person AS SELECT address, phone FROM location;
This time, the output will look like the following:
mydb=# SELECT * FROM person;
address | phone
Philippines | 09264678131
Australia | 0292787135
USA | 142554658792
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