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 statuscommand, which will tell you if the status is
You’ll also need to have
psqlinstalled. To verify whether this interactive command-line interface is installed, use the command
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:
sudo su - postgres
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:
psql some_username -h 127.0.0.1 -d some_database
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:
CREATE DATABASE dn_name;
Here’s an example:
CREATE DATABASE testdb;
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:
CREATE TABLE users(
ID INT PRIMARY KEY NOT NULL,
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
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:
INSERT INTO TABLE_NAME(COL1, COL2) VALUES(VAL1, VAL2);
INSERT statement will insert six user records into the PostgreSQL table we created earlier:
INSERT INTO users(id, username, password)
VALUES(1001, 'HelloWorld', 'secret'),
(1002, 'IamGreat', 'pass123'),
(1003, 'test', 'sample'),
(1004, 'sampleuser', '123456'),
(1005, 'admin', 'password'),
(1006, 'superadmin', 'pass123');
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:
SELECT COUNT(*) FROM users;
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
SELECT COUNT(*) FROM users
WHERE LENGTH(username) = 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
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started