How to create a VIEW in PostgreSQL

In PostgreSQL, 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

Substitute the 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 view_name.