PostgreSQL Count Function

In most cases, when working with databases, our interest is the actual records stored in the database. However, an instance may occur where we need to determine the number of records in a specific column or entire table.

To achieve this, we can define a custom function or use the built-in count function. In this guide, we will discuss the count function and various examples of how to use it.

Basic Usage

The syntax for this function is:

SELECT count(aggr_expr) FROM table;

Since the count function is an aggregate function, you can pass a wildcard or a specific expression that the function will count. The expression passed to the function as an argument must not return a NULL value.

If an expression returns a NULL value, the function does not count the result.

PostgresSQL Count Function Examples

Let us illustrate how to use the count function using various examples.

Example 1

The first example is the count function with a wildcard. In such an example, Postgres will scan the entire table sequentially and return the number of rows.

An example is:

SELECT count(*) FROM film;
(1 row)

The result above shows that the table contains 100 records.

If you perform the above operation in a table with many records, the query will be slow.

Example 2

Another example is to count the rows in a specific column. You can do this by passing the column name to the count function as:

SELECT count(amount) FROM payment;
(1 row)

Example 3

We can also use the example above but only return the number of distinct values in the column. For example:

SELECT count(DISTINCT amount) FROM payment;
(1 row)

The above shows 19 unique values in the amount column of the payment table.

Example 4

Suppose you want to get the number of records that meet a specific criterion. In such a case, you can pass a conditional statement to the statement as:

SELECT count(*) FROM film WHERE replacement_cost > 15;
(1 row)

The query above should return the number of films where the replacement_cost is greater than 15.


This tutorial has taught you how to use the count query to get the number of rows that meet a specific criterion. Remember, experimentation is the key to mastery.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list