PostgreSQL is an efficient and flexible database that fits a wide variety of use cases. In this guide, you will learn about different ways to query a PostgreSQL database so that you can create a simple and expressive backend to your app or library.
Let's dive in!
The SELECT statement is the fundamental building block of querying your PostgreSQL database. SELECT statements can include several clauses. Some of the more important clauses include:
The following query showcases a SELECT statement that uses all of the clauses mentioned above.
1SELECT id, first_name, last_name 2FROM person 3WHERE age > 20 4GROUP BY country 5ORDER BY last_name 6LIMIT 10
The above query will grab the first 10 people over the age of 20 from the
person table ordered by their surname and grouped by the country in which they reside. As you can see, a set of columns follows the SELECT keyword in the query. From there, you can specify one or more tables using the
FROM clause. The
WHERE clause is used to filter your returned rows. The
GROUP BY clause is used to group rows together by one or more columns, while the
ORDER BY clause is used to order rows in ascending or descending order. The
LIMIT clause is used to limit the number of rows returned by the query.
There are many more clauses that can be used. Some of these include the different joining clauses and the
OFFSET clause, but these are beyond the scope of this guide. For more information, please check out the documentation.
WITH queries, or common table expressions, provide a succinct way to create temporary tables that are only kept in memory until the final query is completed.
WITH queries are often much faster and more readable than creating multiple temporary tables from which to query as well. The below SQL uses a common table expression to select the average ages for dogs and people by their countries.
1WITH dog_ages AS ( 2 SELECT AVG(age) AS avg_dog_age, country 3 FROM animals 4 GROUP BY country 5 WHERE species = 'dog' 6), person_ages AS ( 7 SELECT AVG(age) as avg_person_age, country 8 FROM person 9 GROUP BY country 10) 11SELECT da.avg_dog_age, pa.avg_person_page, da.country 12FROM dog_ages da 13LEFT JOIN person_ages pa ON pa.country = da.country;
As you can see, common table expressions are a powerful and expressive means of querying your PostgreSQL database. Remember that anywhere you are using a temporary table, you can probably use a common table expression instead and get faster results!
The view is PostgreSQL's way of encapsulating queries such that they can be reused in different parts of your database schema. A view makes it so that your query can be used by other queries, triggers, or functions all across your database. The below SQL is an example of a view that is used to query our mocked
1CREATE VIEW vw_person_seniors AS 2 SELECT p.id, p.age, p.first_name, p.last_name 3 FROM person p 4 WHERE age >= 65;
Apart from making your queries reusable, views are a great means of making your backend code more readable and maintainable. By creating views, you can structure your queries such that they are separated out by features found within your app. This can dramatically improve the maintainability and separation of concerns within your app's architecture.
A materialized view is a view that is immediately executed at the time of creation. The results of the query executed by the view are stored on disk and are retrieved when the materialized view is queried. This is huge in terms of performance! A query that takes seconds can be reduced to milliseconds. Below, you can see an example materialized view.
1CREATE MATERIALIZED VIEW mat_person AS 2 SELECT p.id, p.age, p.first_name, p.last_name 3 FROM person p 4 INNER JOIN blog_posts bp ON bp.person_id = p.id;
The biggest downside to using materialized views is that, unless you refresh them by running
REFRESH MATERIALIZED VIEW <mat_table>, you may have stale data when you query the view. This is why materialized views work best when the data you are querying is mostly static and rarely updated.
In this guide, you learned all about the key methods for querying data in PostgreSQL. You learned how SELECT queries comprise the fundamental base for querying a PostgreSQL database and how CTEs (Common Table Expressions) can be used to combine temporary tables together. Building on top of the SELECT query, you learned how to create PostgreSQL views to organize your queries in an elegant manner. You also learned how to utilize materialized views to gain massive performance boosts to your queries when the possibility of stale data is not a problem for your user base.
You can now be confident when it comes to querying your PostgreSQL database. For more information, please check out the PostgreSQL documentation.