December 6, 2021 In PostgreSQL, views act as virtual tables. Although they have rows and columns, we can’t insert any data into them manually. Instead, PostgreSQL runs an underlying
9.2k
By Kate Angelou
In PostgreSQL, views act as virtual tables. Although they have rows and columns, we can’t insert any data into them manually. Instead, PostgreSQL runs an underlying query when we refer to the view.
Creating views with PostgreSQL
Let’s create a table of users to use it in a view.
CREATETABLEusers(
idserialPRIMARYKEY,
emailtextUNIQUE,
is_email_confirmedboolean
)
Now, let’s define a view that contains all confirmed emails. To do that, we need to use the CREATEVIEW command followed by a query that describes our view.
CREATEVIEWconfirmed_emailsAS
SELECTemailFROMusers
WHEREis_email_confirmedIStrue
When we select rows from the above view, we see a table of emails.
SELECT*fromconfirmed_emails
The crucial thing to understand is that even though views resemble tables, they are not stored in the database. We can easily prove that with the EXPLAIN command that returns the execution plan. With it, it shows us how PostgreSQL scans the table.
EXPLAINSELECT*fromconfirmed_emails
Above, we can see that running SELECT*from confirmed_emails caused the database to query all users and filter out those who don’t have their email confirmed.
Managing columns included in the view
By default, PostgreSQL deduces the columns of the view from the query. In the above example, we’ve used SELECT email FROM users, and because of that, our view only has one column called email.
We need to be careful when structuring queries used in our views. Unfortunately, some queries can produce unexpected results.
CREATEVIEWview_nameASSELECT'Lorem ipsum'
The above query results in creating a column called ?column?.
To deal with the above problems, we can provide a list of column names we want PostgreSQL to use.
CREATEVIEWconfirmed_emails(email,domain)
ASSELECTemail,split_part(email,'@',2)FROMusers
WHEREis_email_confirmedIStrue
Above, we’re using the split_part function that splits text on a given separator.
PostgreSQL knows that the second column is called the domain, thanks to providing a list of column names.
Another way to achieve the above outcome would be to use the AS keyword.
Views can help simplify queries that would be lengthy and difficult to read otherwise. We need to keep in mind that, by default, PostgreSQL keeps the definitions of all of the views we’ve created. We can use the DROP VIEW command to clean up after our queries.
CREATEVIEWconfirmed_emailsAS
SELECTemailFROMusers
WHEREis_email_confirmedIStrue;
-- Perform operations on the confirmed_emails view
DROPVIEWconfirmed_emails;
Fortunately, PostgreSQL allows for a more elegant solution. With the TEMPORARY keyword, we can create views that PostgreSQL automatically drops at the end of the current session.
CREATETEMPORARYVIEWconfirmed_emailsAS
SELECTemailFROMusers
WHEREis_email_confirmedIStrue;
-- Perform operations on the confirmed_emails view
If our view refers to temporary views or tables, it becomes a temporary view out of the box even without the TEMPORARY keyword.
Materialized views
By default, views don’t persist data into the database. This behavior changes when we create a materialized view.
Let’s create a table of posts that keeps the creation date.
CREATETABLEposts(
idserialPRIMARYKEY,
titletext,
contenttext,
created_attimestamptzDEFAULTnow()
)
If you want to know more about dates, check out Managing date and time with PostgreSQL and TypeORM
Having the above table, we can create a materialized view that holds all of the posts from yesterday.
CREATEMATERIALIZEDVIEWposts_from_yesterdayAS
SELECT*FROMposts
WHEREcreated_at<timestamptz'yesterday'
Thanks to the above code, the posts_from_yesterday view contains all of the posts from yesterday. PostgreSQL stores a table of yesterday’s posts in the database because posts_from_yesterday is a materialized view. Therefore, we don’t need to filter all posts every time we fetch them.
EXPLAINSELECT*FROMposts_from_yesterday
The crucial thing is that materialized views don’t update automatically. Because of that, there is a risk they contain outdated data. For the materialized view to update, we need to refresh it manually.
REFRESHMATERIALIZEDVIEWposts_from_yesterday
Because of that, materialized views might come in handy when we want to cache the results of some complex queries. For example, we could refresh the posts_from_yesterday view once a day to keep it up to date.
Creating views with TypeORM
To create views with TypeORM, let’s define a table of users first.
When we do the above, PostgreSQL counts the domains used by our users.
Materialized views
The official TypeORM documentation doesn’t mention anything about materialized views. However, TypeORM supports materialized views.
To create a materialized view with TypeORM, we need to use the materialized:true flag:
1
2
3
4
5
6
7
8
9
10
11
12
import{ViewColumn,ViewEntity}from'typeorm';
@ViewEntity({
expression:`
SELECT*FROM posts WHERE created_at<timestamptz'yesterday'
`,
materialized:true
})
exportclassPostsFromYesterday{
@ViewColumn()
domain:string;
}
Unfortunately, to refresh the view, we need to use the following query manually:
REFRESH MATERIALIZED VIEW posts_from_yesterday
Summary
In this article, we’ve gone through various types of views both through SQL and TypeORM. Views can come in handy when we have a lot of complex queries and want to make them more readable. Also, we can use views to help us refactor existing tables. For example, if we’re going to deprecate an old table and move to a new one, we can create a view in the place of the old table for some time. Also, we can give access to views to the users while the tables are not accessible directly. All of the above make views a tool worth knowing.