Using the SELECT Statement to Query Records in a PostgreSQL Table
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.
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 statuscommand. 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
psql to interact with PostgreSQL in this tutorial. You can use the following command in a terminal window to access PostgreSQL using
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
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
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:
CREATE TABLE TABLE_NAME(column_name + DATA TYPE + constraints IF any)
For the purposes of this article, we’ll create a table named
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:
NOTE: It’s also possible to
SELECT multiple columns in PostgreSQL.
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
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;
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