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 rows that match a specific condition of a query. The following statement illustrates various ways of using the COUNT function.
1 | SELECT COUNT(*) FROM table_name; |
The COUNT(*) function returns the number of rows returned by a SELECT statement.
When you apply the COUNT(*) function to the entire table, PostgreSQL has to scan the whole table sequentially. If you use the COUNT(*) function on a big table, the query will be slow. This 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.
1 | SELECT COUNT(column) FROM table_name; |
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.
1 | SELECT COUNT(DISTINCT column) FROM table; |
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, you use the following query:
1 2 3 4 | SELECT COUNT (*) FROM payment; |
PostgreSQL COUNT with DISTINCT
To get distinct amounts that customers paid, you use the COUNT function with DISTINCT as follows:
1 2 3 4 | SELECT COUNT (DISTINCT amount) FROM payment; |
PostgreSQL COUNT with GROUP BY clause
To get the number of payments by customer, you use the GROUP BY clause to group the payments into groups based on customer id, and use the COUNT function to count the payments for each group.
The following query illustrates the idea:
1 2 3 4 5 6 7 | SELECT customer_id, COUNT (customer_id) FROM payment GROUP BY customer_id; |
PostgreSQL COUNT with HAVING clause
You can use the COUNT function in a HAVING clause to apply a specific condition to groups. For example, to return customers who have made more than 40 payments, you use the following query:
1 2 3 4 5 6 7 8 9 | SELECT customer_id, COUNT (customer_id) FROM payment GROUP BY customer_id HAVING COUNT (customer_id) > 40; |
In this tutorial, you have learned how to use the PostgreSQL COUNT function to return the number of rows in a table.