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 isactive
.You’ll also need to have
psql
installed. To verify whether this interactive command-line interface is installed, use the commandpsql -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:
1 | 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:
1 | 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:
1 | CREATE DATABASE dn_name; |
Here’s an example:
1 | 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:
1 2 3 4 5 | CREATE TABLE users( ID INT PRIMARY KEY NOT NULL, username VARCHAR(50), password VARCHAR(50) ); |
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:
1 | INSERT INTO TABLE_NAME(COL1, COL2) VALUES(VAL1, VAL2); |
The following INSERT
statement will insert six user records into the PostgreSQL table we created earlier:
1 2 3 4 5 6 7 | 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:
1 | 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:
1 2 3 4 | COUNT ------- 6 (1 ROW) |
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
:
1 2 | 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 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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started