Count Rows in PostgreSQL

In PostgreSQL database server, some common problems arise are how to retrieve data from PostgreSQL itself. We can use tools or utilities based on GUI (Graphical User Interface) such as PgAdmin or any other tools available.

Certainly those tools cannot be used if we do not know how to use it. Especially if the concern is regarding on the query language which is needed to be used to retrieve certain data. So, it is not depend on what kind of interface we have to connect and retrieve data but actually it depends on how can we able to communicate with the database server, in this context PostgreSQL database server.

In this article, we will discuss a simple query language which is used to count number of records available in a single table of a database exists in PostgreSQL database server. Below is the pattern :

select count(*) from table_name

Off course, to be able to execute a single and simple query, we have to be connected with PostgreSQL database server. In this context, I am using PostgreSQL command console to execute the query. It doesn’t make any differentiation to execute that query either in text mode inside a PostgreSQL command console or in GUI (Graphical User Interface) tools such as PgAdmin. .

I will show to execute the above query inside a PostgreSQL command console. Below are steps to do it :

1. Login to PostgreSQL command console as  shown below :

user@hostname:~$ psql -Upostgres
Password for user postgres: 
psql (9.5.3, server 9.3.10)
Type "help" for help.

postgres=# 

2. After successfully connected to PostgreSQL database. Since we don’t specify database name in the parameter command above to login to PostgreSQL database server. We will be faced with all databases which is permitted to be accessed based on the role of the user which is used to log in and in this case is ‘postgres’. Read the article on how to list available databases in PostgreSQL in this link.

3. Soon as we knew all available databases that can be accessed, choose the database instance first to be selected. Read the article on how to select a single database to be accessed in this link.

4. Execute the command to list available tables in the database which is selected. Read the article on how to list available tables in a database in this link.

5. Finally, execute the above command to count how many records are there in the table as shown below :

db_name=# select count(*) from table_name;
count
-------
0
(1 row)

db_name=#

db_name : it represents the database name which is being selected for further access.
table_name : it represents the name of a table exists inside the database which is being selected.