Using the SELECT Statement to Query Records in a PostgreSQL Table

Introduction

If you’re getting started with PostgreSQL, one of the first things you’ll want to learn is how to retrieve data from your database. In SQL, the SELECT statement is the command used to query a database and have results returned; the best way to become familiar with this SQL command is through some realistic examples. In this tutorial, we’ll provide multiple examples that demonstrate how to use the SELECT statement to query records in a PostgreSQL table.

Prerequisites

Before we can execute any SELECT statements in PostgreSQL, a few key prerequisites need to be in place:

• First, make sure that PostgreSQL is installed. To check if PostgreSQL is already installed on your machine, use the service postgresql status command. You can then press CTRL + C to exit.

• You’ll also need to make sure psql, the command-line interface for PostgreSQL, is installed on your machine. You can check by typing psql -V, which will also tell you the version number that’s installed.

Go into the ‘psql’ command-line interface for PostgreSQL

We’ll use psql to interact with PostgreSQL in this tutorial. You can use the following command in a terminal window to access PostgreSQL using psql:

You’ll be prompted for the user’s password. Once you input the password and press Return, you should have access to the specified database.

Create a PostgreSQL Database

Once you’ve gotten into the psql interface, use the following SQL statement to create a new database:

We’ll name our new database testdb:

Now that we have created our database, we can see it in the list of all databases that can be retrieved by using the command \l .

Connecting to a Database in ‘psql’

If you entered the psql interface without specifying a database, or if you decide to connect to a different database than the one you originally specified, you can use the following command to connect to a specific database:

In this example, we connect to our newly-created database:

Create a New PostgreSQL Table

After connecting to our new database, we can create a table to store data that we’ll use in our examples.

The syntax for the SQL command to create a table is:

For the purposes of this article, we’ll create a table named employee:

Insert Records in the PostgreSQL Table

Our next step is to insert some records into our new employee table. We’ll be able to use SELECT statements to query these records in PostgreSQL.

Here’s the basic syntax for inserting a record:

For our example, we’ll be inserting the following record:

NOTE: Do not use ' ' if your column data type is an integer.

Using the ‘SELECT’ Statement in PostgreSQL

We can query a table using a SELECT statement to show data from just one column The example below uses the SELECT statement to find the name of all employees:

This statement will return a result like this:

NOTE: It’s also possible to SELECT multiple columns in PostgreSQL.

For example:

This query will return a result like the following:

You can also view the data from all columns in a table using the SELECT statement in PostgreSQL:

NOTE: The asterisk (*) represents ALL in a SQL statement.

You can even use the SELECT statement in PostgreSQL with mathematical expressions, as seen below:

Conclusion

When you’re working with a PostgreSQL database, learning how to perform queries is an essential skill. In this article, we created a sample database and table and showed multiple examples of the SELECT statement in action. With these examples to guide you, you’ll be prepared to create your own SELECT statements to query records in a PostgreSQL table.