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.

Screenshot of a terminal window getting the PostgreSQL server status and psql version

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:

psql someUserName -h 127.0.0.1 -d some_database

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:

CREATE DATABASE database_name;

We’ll name our new database testdb:

CREATE 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:

\c 'database_name'

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

\c testdb;

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:

CREATE TABLE TABLE_NAME(column_name + DATA TYPE + constraints IF any)

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

CREATE TABLE employee(id INT NOT NULL PRIMARY KEY, emp_fullname text, emp_address CHAR(50), salary REAL, join_date DATE);

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:

INSERT INTO TABLE_NAME(column1, column2, column3, column4, column5)
VALUES('', '', '', '', '');

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

INSERT INTO employee(id, emp_fullname, emp_address, salary, join_date)
VALUES(1, 'John Smith', 'California', 60000, '01-12-2001');

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:

SELECT emp_fullname FROM employee;

This statement will return a result like this:

emp_fullname
-----------------
John Smith
Blackburn Scott
Jack Frost
Aliga Rose
Jack Jill
David Flack
Mary Ann
Patricia Dulay
Jeniffer Sue
Nancy Barbara
(10 ROWS)

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

For example:

SELECT emp_fullname, salary, join_date FROM employee;

This query will return a result like the following:

emp_fullname | salary | join_date
-----------------+--------+------------
John Smith | 60000 | 2001-01-12
Blackburn Scott | 45000 | 2011-01-30
Jack Frost | 90000 | 1999-05-15
Aliga Rose | 55000 | 2015-07-20
Jack Jill | 50000 | 2016-03-01
David Flack | 24000 | 2017-05-27
Mary Ann | 46000 | 2010-06-14
Patricia Dulay | 50000 | 2016-01-27
Jeniffer Sue | 38000 | 2015-05-13
Nancy Barbara | 70000 | 2012-07-20
(10 ROWS)

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

SELECT * FROM employee;

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

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

SELECT 7 + 3 AS RESULT;

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.