Introduction to Views in PostgreSQL

1. Views in PostgreSQL
2. Create a View
3. Display a View
4. Update a View
5. Delete a View

Although they are beneficial, users sometimes have trouble dealing with views in PostgreSQL. However, this will no longer cause any problems. First, we will describe everything you need to know to create, delete, and update views in PostgreSQL.

Views in PostgreSQL

A view looks like any other table in a database. However, the difference is that one can extract the contents of an existing database and display only that data to a user.

As a result, any sensitive or irrelevant information will remain in the original database and hidden from the public.

Create a View

Creating a view might seem tricky, but PostgreSQL makes it incredibly simple. To do that, you need to use the CREATE VIEW statement accompanied by a condition that can be applied using any query.

Consider the following schema:

CREATE TABLE users(
    user_id int PRIMARY KEY,
    user_name VARCHAR (100) UNIQUE,
    email VARCHAR (300) NOT NULL
);

INSERT INTO users(user_id, user_name, email)
VALUES (1, 'name', 'email');

Let’s say you wanted to display a table to the user where only the user_id and user_name are visible. To achieve this, you may use the code below:

CREATE VIEW view_name AS
SELECT user_id, user_name
FROM users;

It will create a view that displays only the user_id and the user_name from the users table. To retrieve data using view_name, we need to use the SELECT command as follows:

select * from view_name;

Output:

 user_id | user_name 
---------+-----------
       1 | name

If you like, you can also add additional conditions. For example, if you only want a view that displays a specific user, you could try this:

CREATE VIEW view_name AS
SELECT user_id, user_name
FROM users
WHERE user_id= 123;

This view would only consist of those users whose user_id is 123.

Note: A view cannot be created without a corresponding table in PostgreSQL. Always create a table before a view.

Display a View

Displaying a view is almost as simple as creating one. When we want to view a table, we often use the following syntax:

SELECT * FROM users;

In the same way, when we want to display a view, we use a similar syntax.

SELECT * FROM view_name;

Note: During creation, make sure each view name is unique, just like table names.

Display the Code of a View

At times, in large databases or long lines of code, we tend to lose the code of a view and are unable to access it. However, PostgreSQL has commands that can quickly help you display the code without scrolling through multiple lines.

Here are some ways you can get that done:

• SELECT clause - This is one of the more straightforward methods since most users are familiar with the SELECT clause. Here is how you can use it:

  SELECT definition FROM pg_views
  WHERE viewname = 'view_name';
  

  Some versions also accept this:

  SELECT view_definition FROM information_schema.views
  WHERE table_name = 'view_name';
  

  It is essential to note that this command will provide a rewritten version of your original code used to create the view.

• pg_get_viewdef - Although this command works fine, many users have found it challenging to implement. If this is the scenario for you as well, try using it as follows:

  SELECT pg_get_viewdef('view_name', true);
  

  Another method is to cast a name to regclass. Here are some ways that we can use it:

  SELECT pg_get_viewdef('view_name'::regclass);
  

• \d+ viewname - Some of the commands above can be a little tricky to remember, even though they work well. If you would prefer something quick and effective, try this command instead.

  If you want to explore more functions such as this one, try typing \? in the command line. It will then display a series of commands you can play around with to discover more features in PostgreSQL.

Update a View

If you know the name of a view, you can constantly update it even if you can’t access the original code used for creation.

It can be achieved using the UPDATE clause. But, first, create a view using an existing table, as explained earlier. Please remember the name of the view, or you won’t be able to access it. Try the following to update your view:

CREATE OR REPLACE VIEW view_name AS 
SELECT * FROM name_of_the_table;

You may replace the last line of this code with any query that will help generate your preferred view. If you would like to change the name of your view, you can do so using the following code:

ALTER VIEW view_name
RENAME TO new_name;

You can also update a view by deleting the existing one and creating again with whatever changes are necessary. The deletion process has been explained below.

Delete a View

There are multiple causes why one might want to delete a view. For example, maybe there is so much updating that it’s better to do it over, or perhaps it’s no longer needed.

Whatever the cause, PostgreSQL has made it incredibly simple to delete a view. You may do so with the following code:

DROP VIEW IF EXISTS view_name;

Here, the IF EXISTS clause is not mandatory but recommended. It allows the code to run even if the view name does not exist.

However, it can be a problem if you make an error while listing the view name in a long piece of code, as it could prevent deletion and produce errors in the database. So, the use depends on the nature of your task.

That is everything you need to know to use views on PostgreSQL regarding creation, updating, deletion, display, and accessing creation codes. We hope that you now understand its functions in depth and can easily use them.