PostgreSQL COUNT Function

Summary: in this tutorial, you will learn how to use the PostgreSQL COUNT function to count the number of rows in a table.

PostgreSQL COUNT function overview

The COUNT function returns the number of input rows that match a specific condition of a query. The following statement illustrates various ways of using the COUNT function.

The COUNT(*) function returns the number of rows returned by a SELECT clause. When you apply the COUNT(*) to the entire table, PostgreSQL scans table sequentially. If you use it on a big table, it will be slow. Why? The reason why it is slow is related to the PostgreSQL MVCC implementation. Because multiple transactions see different states of data at the same time, there is no direct way for COUNT(*) function to count across the whole table, therefore PostgreSQL must scan all rows.

Similar to the COUNT(*) function, the COUNT(column) function returns the number of rows returned by a SELECT clause. However, it does not consider NULL values in the column.

In this form, the COUNT(DISTINCT column) returns the number of distinct rows whose the values of the column are not null.

PostgreSQL COUNT function examples

Let’s use the payment table in the sample database for the demonstration.

To get the number of transactions in the payment table, we use the following query:

PostgreSQL COUNT with DISTINCT

To get the number of amounts that customers paid, we use the COUNT function with DISTINCT as follows:

PostgreSQL COUNT with GROUP BY clause

To get the number of transactions per customer, we use GROUP BY clause to group the transactions into groups based on customer id, and use the COUNT function to count the transactions for each group. The following query illustrates the idea:

PostgreSQL COUNT with HAVING clause

You can use the COUNT function in a HAVING clause to apply a specific condition for groups. For example, to return customers who have more than 40 transactions, you use the following query:

In this tutorial, you have learned how to use the PostgreSQL COUNT function to return the number of rows in a table.