view is a
virtual table. It represents the result of a SELECT query and can be used to quickly get access to data without having to execute the full query.
In the previous article, we’ve talked about how to save a SELECT query as a function in PostgreSQL.
Creating a view is an alternative solution that might better suit your needs.
How to create a view in PostgreSQL
Creating regular views is a lot more straightforward when compared to stored procedures or functions.
To save your SELECT query as a view you should just add the following line
CREATE VIEW view_name AS
view_name with something that represents the data being selected, follow it by the SELECT query, and you’re good to go.
If we imagine we have the table
users and each user has the property
follower_count, then we might be interested in creating a view that stores
popular_users, the ones who have more than 1000 followers.
CREATE VIEW influencers AS SELECT name, email, created_at FROM users WHERE follower_count > 1000;
You’re not limited in any way in complicating the SELECT query further. You can add joins, aggregations, or anything else you might think of. The only exception is the
ORDER BY clause which is unavailable for views.
A view will look like a table in your database, which is very convenient if you use a GUI
How to delete a PostgreSQL view
You can delete a view in PostgreSQL you can use a syntax similar to dropping a table
DROP view [ IF EXISTS ] view_name;
IF EXISTS flag is optional and including it will prevent you from errors that might be raised if the view doesn’t exist, i.e. you’ve made a typo in