Creating a View in PostgreSQL

A View is a way of protecting your SQL tables and formatting your table’s raw data into useful information that can be queried by your organization individuals.

PostgreSQL logo

Contents

What is a View?

Technically, a view is a pseudo-table which means it is not actually (Or necessarily) a table by itself. It’s just a way to load the data from your tables in a helpful way. A view can have all the columns from a table, a query that combines different tables, or some columns of a table in particular, this will depend on what you want or need to be displayed in the view itself.

Creating a View in PostgreSQL

Now, let’s go right to the point and start creating our first View in PostgreSQL:

CREATE OR REPLACE VIEW users_v AS
SELECT * FROM users;

Ok, that’s it! You have created your first view in PostgreSQL. Quite easy, right? Now let’s dive more into it

Creating a View in PostgreSQL with a JOIN statement

As we said before, a VIEW is not necessarily a table, it could also be a combination of tables

CREATE OR REPLACE VIEW users_points_v AS
SELECT a.user_id, SUM(b.user_points) as user_points
FROM users a
LEFT JOIN users_points b
ON a.users_id = b.users_id
GROUP BY 1

Supposing we have a table called users with the users info and other table called users_points with the users_points (And the foreign ket users_id, this query will sum all user_points and display it in this view…

How do I query my View?

This is actually very simple, querying a View is exactly the same than querying a table. And how we call the values? With the same name we define.

SELECT * FROM users_points_v WHERE user_id BETWEEN 1 and 10 AND user_points > 10;

How do I drop my view?

Very simple too, we just need to DROP the View.

DROP VIEW users_points_v;

How do I modify my view?

First of all, is important that you know that this will depend in your PostgreSQL permissions, normally, a simple user won’t have access to drop the views, and depending on your organization size, you will also only have read access for the corporate views and no access at all to the corporate tables.

As we see before, I am using the command CREATE OR REPLACE View, this means that if the View is already created, it will be replaced with the new view.

CREATE OR REPLACE VIEW users_v AS
SELECT * FROM users;

How to display how my View was created

Sometimes we need to know how the view was created, in this case, we can use the SHOW command that will specify how our view was generated.

SHOW VIEW users_points_v;

The WITH command in VIEWs

Ok, this is a little tricky but let’s say you want to define multiple subqueries in a view, the best way to do this is using the WITH command. We could also use subqueries, but it is more organized and neet this way. (By the way, the WITH command is a statement that is used only in some SQL engines, this won’t work in all SQL Views)

CREATE OR REPLACE VIEW user_points_by_date_v
SELECT * FROM
(
WITH points AS (
SELECT user_id, points_date, SUM(user_points) AS points  FROM users_points
GROUP BY 1,2
),
gregorian_dates_vals AS (
SELECT not_gregorian_date, gregorian_date_conversion as gregorian_date FROM gregorian_dates
)
SELECT user_id, greg.gregorian_date, p.points FROM
users a
LEFT JOIN points p
ON a.user_id=p.user_id
LEFT JOIN gregorian_dates_vals greg
ON p.points_date = greg.not_gregorian_date
) WITH NO SCHEMA BINDING;

Ok, this last one was complicated… Here what we do is generate two “sub-tables” with the WITH command one is called points and the other gregorian_dates_vals and then, at last, we create a query that will join these subtables with our main table which is users.

At last, we are referencing WITH NO SCHEMA BINDING, this means that in case one of the tables doesn’t exist, the view will exist either way (But in this case in particular, as all depend on all, probably will come with null values)

Conclusion

We saw how to create views in PostgreSQL here. It’s actually very interesting and has a lot of potential. The views actually get more and more useful as your organization grows and needs better ways to protect the information with user permissions.

If you are new to PostgreSQL I would recommend you create your own test environment.

We have dived into the basics of creating a View but Here you have more information about views and their different variables.

Leave a Reply