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 typingpsql -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
:
1 | 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:
1 | CREATE DATABASE database_name; |
We’ll name our new database testdb
:
1 | 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:
1 | \c 'database_name' |
In this example, we connect to our newly-created database:
1 | \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:
1 | CREATE TABLE TABLE_NAME(column_name + DATA TYPE + constraints IF any) |
For the purposes of this article, we’ll create a table named employee
:
1 | 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:
1 2 | INSERT INTO TABLE_NAME(column1, column2, column3, column4, column5) VALUES('', '', '', '', ''); |
For our example, we’ll be inserting the following record:
1 2 | 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:
1 | SELECT emp_fullname FROM employee; |
This statement will return a result like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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:
1 | SELECT emp_fullname, salary, join_date FROM employee; |
This query will return a result like the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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:
1 | 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:
1 | 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