How to Connect to PostgreSQL and Get the Table Row Count

Introduction to querying PostgreSQL table data with psql

When you’re working with PostgreSQL, there may be times when you need to find out how many rows are in a given table. Fortunately, it’s easy to obtain this information using a simple SQL statement. In this article, we’ll show you how to use the psql command-line interface to connect to PostgreSQL and get a table row count. We’ll be using a SQL SELECT statement in conjunction with the COUNT() function to accomplish this task.

Introduction to the SQL ‘SELECT’ function in Postgres

In SQL, a SELECT statement returns any Postgres records that match the query passed to it. We can also use SELECT in conjunction with the COUNT() SQL function to return a tally of the queried PostgreSQL records. This article will demonstrate how we can do this in PostgreSQL to get a table row count.

Prerequisites to connecting to PostgreSQL

Before we attempt to connect to PostgreSQL and a get a table row count, we need to go over a few key prerequisites for the task:

• You’ll need to have PostgreSQL installed on your machine. To confirm that it’s installed, use the service postgresql status command, which will tell you if the status is active.

• You’ll also need to have psql installed. To verify whether this interactive command-line interface is installed, use the command psql -V.

Accessing the PostgreSQL using the ‘psql’ command-line interface

Let’s look at the basic syntax that’s used to access a PostgreSQL database on a localhost server:

You’ll be prompted for the password. Once you input the password and press RETURN, you’ll have access to Postgres.

Let’s look at an example of how to connect to PostgreSQL with a username and database name:

Create a PostgreSQL database

If you haven’t already created a database in PostgreSQL, you’ll need to create one to use in this tutorial. After entering the psql interface, you can use the following command to create a database in PostgreSQL:

Here’s an example:

We can use the \l command in psql to show a list of all of the databases. We can also use the command \c followed by the database name to connect to the database that we created earlier.

Create a PostgreSQL table and insert some data

Before we can use any commands in PostgreSQL to get a table row count, we’ll need to create a table within our database.

The SQL statement shown below creates a simple table that we can use in our examples:

This statement should return a response saying CREATE TABLE, confirming that the table was created. If you want to see a list of all the tables in your Postgres database, you can use the \du command.

Inserting data to the PostgreSQL table

Now, let’s use an INSERT statement to add some records to our table. Here’s the basic syntax:

The following INSERT statement will insert six user records into the PostgreSQL table we created earlier:

This SQL statement should return a response of INSERT 0 6. The 6 indicates that the six records were inserted successfully into the table, and the 0 tells us that zero object identifiers (OIDs) were assigned to the rows.

Using the ‘SELECT’ statement in PostgreSQL to get the table row count

At this point, we have a working table with several records added to it. We can now use the SELECT statement in conjunction with the COUNT() function to get the total number of records.

The basic syntax used in PostgreSQL to get a table row count is shown below:

NOTE: The asterisk is used as a substitute for parameterless queries in functions like COUNT(). When you use the asterisk, the COUNT() function will return all rows, even if they contain null values.

In our example, the output should return the value of 6 with a response of count above the number of rows:

Count all the Postgres table records that match a condition

We can also use the WHERE keyword in a SELECT statement to count only the table records that match the query criteria in the statement.

The following example shows how we can tally the number of records in our users table that have a username where the length equals 4:

Because we inserted a record with the values (1003, 'test', 'sample') earlier in this tutorial, our expected output should at least be (1 row) because the value of username for that record is test.

Conclusion to using psql for PostgreSQL to get the table row count

When you need to find out how many records are in a particular table, it’s important to know how to obtain that information quickly. With PostgreSQL, it’s easy to count the rows in a table with a simple SQL statement. In this tutorial, we learned how to use PostgreSQL to get a table row count using a SELECT statement with the COUNT() function. With the instructions provided in this tutorial, you’ll be prepared to create similar statements to get information about your own PostgreSQL tables.